topic Re: Pivot table aggr sort CHALLENGE in QlikView

Pivot table aggr sort CHALLENGE

scotthan — Fri, 14 Sep 2012 13:13:28 GMT

Hi team,

I have a question on sorting in a pivot table where I have used aggr in two calculated dimensions.

I have provided an example with limited info where I only want to show the top 5 clients by count, and only the top 5 issuers by count for those clients. I believe I have achieved that with these two dimensions and one expression

if(aggr(rank(aggr(count(ClientName),ClientName)),ClientName)<=vTopClients,ClientName)

if(aggr(rank(aggr(count(ClientName), ClientName,IssueName)),ClientName,IssueName)<=vTopIssuers,IssueName)

count(ClientName)

I have also properly sorted the first column (Clients) using

rank(aggr(count(ClientName),ClientName))

I am stuck on the last part, which is sorting the second column. I can't seem to get the Issuers to sort correctly by count from greatest to least. My chart is showing the Issuers with the top five counts just not in order! Ugh. I provided references tables on the right so you will know when you have sorted it correctly. Jellyfish needs to be the second client shown..

Will you help me sort this please?? Thanks!

Re: Pivot table aggr sort CHALLENGE

Fri, 30 Nov 2012 06:57:22 GMT

I am facing the same issue. Did you figure out how to solve this ?

Re: Pivot table aggr sort CHALLENGE

hic — Fri, 30 Nov 2012 11:41:32 GMT

The current situation is that the table created by the aggr function (in your case count(ClientName), ClientName,IssueName) is always sorted by the load order of the grouping fields (ClientName,IssueName). I have looked at this problem myself and I have not found any way to sort it differently.

We (QlikTech) also see the need to be able to specify the sort order and we are looking at ways to solve this in future versions.

HIC

Re: Pivot table aggr sort CHALLENGE

IAMDV — Sun, 02 Dec 2012 23:09:00 GMT

HIC - Just thinking on what you said, if the Aggr Function sorts on Load Order then how about if we create a pseudo table with required sort order for the sake of Load Order and drop it before we load the original table. Something like this...

SortOrderTable:

LOAD FinalClientName,

Count(ClientName) as MyCount

from TestData.qvd(qvd)

Group by ClientName

order by ClientName Desc;

Drop Table SortOrderTable;

....

TestData:

LOAD applymap('ClientMap',ClientName) as FinalClientName,

capitalize(applymap('IssuerMap',IssueName)) as IssueName

from TestData.qvd(qvd);

And then Load the original table with the data. I could have tried it if I have full data.

Do you think something like this works? Because QlikView remembers the Load Order when it loads for the first time. So instead of using ClientName we can use FinalClientName within our expressions which contains right Load Order.

Thanks,

www.QlikShare.com

Re: Pivot table aggr sort CHALLENGE

hic — Mon, 03 Dec 2012 08:42:05 GMT

Yes, I think it would work. But it would be a static definition of the sort order, which perhaps isn't what you want. You probably want the order to change as you make selections.

However, your example assummes an "order by" when loading from a qvd. That will not work. "Order by" only works on "Load ... resident". (Unfortunately...) So, you would have to do it in two steps: Either two qvw:s (the first one creates the qvd with the right order, the second uses the qvd.) Or, using a resident load like

PreSortOrderTable:

LOAD ClientName as TempClientName,

Count(ClientName) as MyCount

from TestData.qvd(qvd)

Group by ClientName;

SortOrderTable:

LOAD TempClientName as ClientName

resident PreSortOrderTable

order by MyCount Desc;

Drop Table PreSortOrderTable, SortOrderTable;

HIC

Re: Pivot table aggr sort CHALLENGE

IAMDV — Mon, 03 Dec 2012 08:47:45 GMT

Thank you HIC. My bad! that was wrong example of sorting on QVD but you are right we need to use Resident Load approach and indeed it is expensive operation. But I can't think of anything apart from these two options.

Thanks again.

Cheers,

topic Re: Pivot table aggr sort *CHALLENGE* in QlikView

Pivot table aggr sort *CHALLENGE*

Re: Pivot table aggr sort *CHALLENGE*

Re: Pivot table aggr sort *CHALLENGE*

Re: Pivot table aggr sort *CHALLENGE*

Re: Pivot table aggr sort *CHALLENGE*

Re: Pivot table aggr sort *CHALLENGE*

topic Re: Pivot table aggr sort CHALLENGE in QlikView

Pivot table aggr sort CHALLENGE

Re: Pivot table aggr sort CHALLENGE

Re: Pivot table aggr sort CHALLENGE

Re: Pivot table aggr sort CHALLENGE

Re: Pivot table aggr sort CHALLENGE

Re: Pivot table aggr sort CHALLENGE