Seven Stars

Aggregate rows - getting null

Hi,

 

What I'm trying to do is after aggregating the rows, there must be a two results - one containing result of aggregation and one containing unaggregated row.

 

1. Problem

When I used tAggregateRow component, it has given me the wrong result as in below screenshots:

 

jobcleanpo.PNGDupsss.PNG

Why I'm getting null values in a resulting columns - PODate & VendorNumber ?

 

Thanks !

  • Data Integration
Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Seven Stars

Re: Aggregate rows - getting null

Thanks @rhall_2_0 for let me know tAggregateRow component is not sufficient for my requirement.

 

The solution suggested by you is good but I can't apply in my case as raw data has no any fix data type of any column, so sort would be not a good idea. Well, I've achieved the result using hashing and then inner join in following way:

 

1. Changed schema of tAggregateRow component - removed 2 columns for which I was getting null values

2. Used inner join to check for match and unmatched

 

Solved.PNG

 

Result:

 

solvedres.PNG

 

I don't know if its an efficient solution, but its working perfectly.

 

Thanks Smiley Happy

 

8 REPLIES
Six Stars

Re: Aggregate rows - getting null

Hi,

 

The null values are caused by not including the fields in the group by. On the other hand, when you do include VendorNumber in the aggregation, it will not do the aggregation you wish. What exactly do you expect as result for 11676 ?

 

To distinguish unaggregated records, you could consider adding an extra output field and add an count. When the count==1 then it is not aggregated.

 

 

Seven Stars

Re: Aggregate rows - getting null

Thanks @saukema for your quick reply.

 

"The null values are caused by not including the fields in the group by" - How to do this ? I've tried but not getting desired result.

 

For 11676 I expect the result to be :

11676  |  28-07-2016  |  BLUE3  |  30-09-2016

 

So there should no null.

 

To distinguish unaggregated records, I used tMap and its working fine. The only problem is of null values.

 

 

 

 

 

Ten Stars

Re: Aggregate rows - getting null

So you want to group by PONumber, return the Max LastUpdated date and return the other column values that correspond to the LastUpdated date (Max) returned? This is not so easy with this component on its own. But you can engineer a solution.

 

1) Order your data by PONumber and LastUpdated date using a tSortRow. Ensure that your order by PONumber first and LastUpdated second.

2) In your tAggregate component, group by the PONumber (as in your example) but select the LAST function for ALL output columns (assuming that your ordering is set to order by date ascending in your tSortRow component.

 

This will mean that your max date will be returned with the values that correspond to that.

Rilhia Solutions
Seven Stars

Re: Aggregate rows - getting null

Thanks @rhall_2_0 for let me know tAggregateRow component is not sufficient for my requirement.

 

The solution suggested by you is good but I can't apply in my case as raw data has no any fix data type of any column, so sort would be not a good idea. Well, I've achieved the result using hashing and then inner join in following way:

 

1. Changed schema of tAggregateRow component - removed 2 columns for which I was getting null values

2. Used inner join to check for match and unmatched

 

Solved.PNG

 

Result:

 

solvedres.PNG

 

I don't know if its an efficient solution, but its working perfectly.

 

Thanks Smiley Happy

 

Ten Stars

Re: Aggregate rows - getting null

I'm confused, if you cannot sort because of a lack of fixed data types, how were you able to use the Max function successfully? The Max functionality and Sort functionality are practically the same

Rilhia Solutions
Seven Stars

Re: Aggregate rows - getting null

Data type is not fixed for PONumber column that can be alphanumeric, but in case of LastUpdated column its fixed as type date. (If not, I may need to transform/format it to date before applying any aggregation function.)
Ten Stars

Re: Aggregate rows - getting null

That would suit using the way I suggested, which would be a little bit more efficient than your workaround. But if it works :-)

Rilhia Solutions
Seven Stars

Re: Aggregate rows - getting null

I appreciate your help, and will definitely give a try to your suggestion. Many thanks for your time @rhall_2_0 Smiley Happy