Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Subfield with Rank

Hi All,

I have data like below

  

StringNumber
A-B100-200
C-D300-500
E-F250-200

I want the output like below'

  

StringNumberRank
D5001
C3002
E2503
B2004
F2004
A1005

Note: I want to do it in script

1 Solution

Accepted Solutions
Kushal_Chawda

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

View solution in original post

3 Replies
Kushal_Chawda

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

swuehl
MVP
MVP

Or

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;

dusasuman
Creator
Creator

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