Need to create a Column

[kunkumnaveen]

Hello All,

I need help in creating a column call Rank (Colored in Green) at qlik scripting as Shown below

Conditions:

1)For each ID the rank will start from 1

2)There are multiple row were ID,DAT,TIM are same for such record the top row should get  rank 1 ,followed by 2 ,3 so on

3)There are few rows where ID and DAT are same but TIM is different in such case the latest time should been assigned as Rank 1 as shown above

Please find the attachment  

Thanks

[zhadrakas · Accepted solution]

that should do it:

Source:
LOAD TNR, 
     RNR, 
     ID, 
     DAT, 
     TIM,
     Rank,
     rowNo() as RowNo
FROM ..\test_data.xlsx (ooxml, embedded labels, table is Sheet1);

Final:
NoConcatenate
Load TNR, 
     RNR, 
     ID, 
     DAT, 
     TIM,
     Rank,
     AutoNumber(RowNo, ID) as Rank_Script
Resident Source
order by ID ASC, DAT DESC, TIM DESC, RowNo ASC
;
drop table Source;