topic Re: How do I add a rank field in a load script statement? in QlikView

How do I add a rank field in a load script statement?

Mon, 07 Jan 2013 22:06:09 GMT

Hi All,

I am using the code below to derive a Count field. Is it possible to add a rank for each TechArea2 as a seperate field based on the Count?

Load TechArea2 , count(DISTINCT [Sl. No]) as Count

Resident ABC

Group by TechArea2;

So I want something like for the TechArea2 with the highest count the rank would be 1, then 2,3,4... and so on. I had used a rank() function in the chart but I have to derive this field in the load statemnet.

Can anyone please suggest anything?

Thanks in advance.

Re: How do I add a rank field in a load script statement?

Gysbert_Wassenaar — Tue, 08 Jan 2013 10:32:22 GMT

Something like this should do it:

T1:

Load TechArea2 , count(DISTINCT [Sl. No]) as Count

Resident ABC

Group by TechArea2;

T2:

load TechArea2, Count, rowno() as Rank

resident T1 order by Count desc;

drop table T1;

Re: How do I add a rank field in a load script statement?

Tue, 08 Jan 2013 17:42:10 GMT

Thanks Gysbert. that was a nice trick.

thanks again.