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

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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;

[#3]

Thanks Gysbert. that was a nice trick.

thanks again.