Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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:

 

0683p000009Lvkg.png0683p000009LvNf.png

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

 

Thanks !

Labels (2)
1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Thanks @rhall 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

 

0683p000009Lvmq.png

 

Result:

 

0683p000009Lvmv.png

 

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

 

Thanks 0683p000009MACn.png

 

View solution in original post

9 Replies
Aukema
Creator
Creator

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.

 

 

Anonymous
Not applicable
Author

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.

 

 

 

 

 

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

Thanks @rhall 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

 

0683p000009Lvmq.png

 

Result:

 

0683p000009Lvmv.png

 

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

 

Thanks 0683p000009MACn.png

 

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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.)
Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

I appreciate your help, and will definitely give a try to your suggestion. Many thanks for your time @rhall 0683p000009MACn.png
Anonymous
Not applicable
Author

I solved it in another way which might be easier.

You could add the columns you need to the operation list in the component properties and use one of the appropriate functions like "first" wich will return only the first row value, or "list" if you want to groups the values in a comma separated string.

Check this screenshot:

 

0683p000009M6Tn.png

Regards!