topic Re: Subfield with Rank in QlikView

Subfield with Rank

Tue, 29 Mar 2016 10:22:28 GMT

Hi All,

I have data like below

String	Number
A-B	100-200
C-D	300-500
E-F	250-200

I want the output like below'

String	Number	Rank
D	500	1
C	300	2
E	250	3
B	200	4
F	200	4
A	100	5

Note: I want to do it in script

Re: Subfield with Rank

Kushal_Chawda — Tue, 29 Mar 2016 10:30:23 GMT

try this

Data:

LOAD subfield(String,'-',1) as String,

subfield(Number,'-',1) as Number

FROM TABLE;

LOAD subfield(String,'-',2) as String,

subfield(Number,'-',2) as Number

FROM TABLE;

Final:

noconcatenate

LOAD *,

if(Rowno()=1,1,

if(Number = previous(Number), peek('Rank'),peek('Rank')+1)) as Rank

Resident Data

order by Number Desc;

Drop Table Data;

Edit: Drop table stmt added

Re: Subfield with Rank

swuehl — Tue, 29 Mar 2016 10:32:06 GMT

INPUT:

LOAD Subfield(String,'-',iterno()) as Name, Subfield(Number,'-',iterno()) as Value

WHILE iterNo() <=2;

LOAD * INLINE [

String, Number

A-B, 100-200

C-D, 300-500

E-F, 250-200

];

RESULT:

LOAD *, AutoNumber(Value) as Rank

Resident INPUT

Order by Value desc;

DROP table INPUT;

Re: Subfield with Rank

dusasuman — Tue, 29 Mar 2016 11:03:11 GMT

Hi,

Please find the attached qvw file. Rank would be the result of Rowno() function when we load the table with Descending order.

Hope it helps..

Regards

Suman