How to rank columnvalues in load script

[Anonymous]

Hi all,

I would like to rank my inputdata in column [Nat] (nationality of employees).

[Nat] contains only numbers (1 = The Netherlands, 52 = Belgium, 55 = Germany, etc.)

My data load is as follows:

_______________________________________________________

Person:

SQL SELECT

    [Person_Id],

    [BSN],

    [Voorl],

    [Voorv],

    [Gebdat],

    [Nat],

    [Gesl]

  FROM [Person];

________________________________________________________

For example:

I have 10 employees (7 from The Netherlands, 2 from Germany and 1 from Belgium)

Then I would like to have the next output:

1, 1

55, 2

52, 3

I hope anybody can help me!

Grtz.

Pascal

[vinieme12]

use can use Rowno() in conjunction with Order By clause

[prat1507]

Use

Tab:

Load [Nat], Count([Person_Id])

resident Person

group by [Nat]

Regards

Pratyush

[vinieme12 · Accepted solution]

Refer sample script

Person:

LOAD * INLINE [

    Nat, ppl

    1, 1

    1, 2

    1, 3

    52, 1

    52, 2

    55, 1

];

Ranks:

Load Nat

,count(DISTINCT ppl) as PplCount

Resident Person

Group by Nat;

left join (Ranks)

Load Nat,

PplCount,

rowno() as Rank

Resident Ranks

Order by PplCount DESC;

[Anonymous]

Thanks Vineeth Pujari!