Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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%)
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.
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.
Thanks Sunny. Strange it isn't a perfect match. I will see if others can diagnose that, but this answer was helpful
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.
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!