topic Re: How can I use above() with aggr() in QlikView

How can I use above() with aggr()

Tue, 24 Nov 2015 17:30:17 GMT

Hi Qlikviewers. I am currently trying to solve an issue where when I create a table, I can find a value im looking for (max of column2-column1), but when I try to apply that logic and create a temp table with aggr, there is no way to sort the table, so the value I get doesn't match up.

I am posting an example below of what I'm trying to do. The "KS" number should match the max of the KS column in the table (42.01%)

Re: How can I use above() with aggr()

sunny_talwar — Tue, 24 Nov 2015 17:59:02 GMT

I think to get the same number using the aggregate function, you will need to sort the Score dimension in the script. Something like this:

Table:

LOAD Score as Score1,

Booked,

Bad

FROM

[KSExample.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD Score1 as Score,

Booked,

Bad

Resident Table

Order By Score1;

DROP Table Table;

The reason is that Aggregate always uses the Load sort order. The result I got was very close, but not sure why it doesn't completely match.

Re: How can I use above() with aggr()

Tue, 24 Nov 2015 18:04:56 GMT

Thanks Sunny. Strange it isn't a perfect match. I will see if others can diagnose that, but this answer was helpful

Re: How can I use above() with aggr()

sunny_talwar — Tue, 24 Nov 2015 18:07:46 GMT

I actually extracted the excel file from the application itself, can you try with the data file you have and the script I provided to see what happens? Or if you can provide the excel file, I can check it at my end as well.

Re: How can I use above() with aggr()

Tue, 24 Nov 2015 18:24:38 GMT

Yeah I just tried it with the raw excel file and the numbers match exactly.

This is a nice workaround but I do wish there was a way to sort within the aggr function, rather than having to rely on data load order.

Thanks!