topic Re: How to rank columnvalues in load script in QlikView

How to rank columnvalues in load script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to rank columnvalues in load script

vinieme12 — Fri, 10 Nov 2017 09:58:44 GMT

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

Re: How to rank columnvalues in load script

prat1507 — Fri, 10 Nov 2017 10:02:05 GMT

Use

Tab:

Load [Nat], Count([Person_Id])

resident Person

group by [Nat]

Regards

Pratyush

Re: How to rank columnvalues in load script

vinieme12 — Fri, 10 Nov 2017 10:08:55 GMT

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;

Re: How to rank columnvalues in load script

Anonymous — Fri, 10 Nov 2017 12:55:54 GMT

Thanks Vineeth Pujari!