Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to rank based on date in load script?

Hello,

For example, the raw data looks like this,

LOAD * INLINE [

    F1,    F2     Date

    A,     100    1/8

    B,      90     1/8

    C,     150    2/8

    D,      67     2/8

    E,      69     1/8

    F,       99     3/8

    G,      12     3/8

    H,       9      2/8

];

I want to produce something similar like below

LOAD * INLINE [

    F1,    F2     Date     Rank

    A,     100    1/8          1

    B,      90     1/8          2

    C,     150    2/8          1

    D,      67     2/8          2

    E,      69     1/8          3

    F,       99     3/8          1

    G,      12     3/8          2

    H,       9      2/8          3

];

I dont know how to add the date condition on below Code

if([F2] = peek([F2]), peek(Rank),RecNo()) as Rank

Thank you in advance everyone

stalwar1swuehlQlik Sense App Development & UsageQlik Sense Forums

1 Solution

Accepted Solutions
sunny_talwar

May be this

Table:

LOAD * INLINE [

    F1, F2, Date

    A, 100, 1/8

    B, 90, 1/8

    C, 150, 2/8

    D, 67, 2/8

    E, 69, 1/8

    F, 99, 3/8

    G, 12, 3/8

    H, 9, 2/8

];


FinalTable:

LOAD *,

If(Date = Previous(Date), RangeSum(Peek('Rank'), 1), 1) as Rank

Resident Table

Order By Date, F2 desc;


DROP Table Table;

View solution in original post

8 Replies
dwforest
Specialist II
Specialist II

if([F2] = peek([F2]) and [Date]=peek([Date]), peek(Rank),RecNo()) as Rank

Anonymous
Not applicable
Author

Hi Mr. David,

First of all, thank you for responding to my question. I'm really appreciate tho

I've tried your code but it still sort overall. Do you have any idea why?

I want the 11th rank to reset into 1st rank

Qlik Community TABLE 2.PNG

Thank you in advance!

Thiago_Justen_

Muhammad,

You could try usinng the order by clause:

SAMPLE_DATA:

LOAD * INLINE [

    F1,    F2,     Date

    A,     100,    1/8

    B,      90,     1/8

    C,     150,    2/8

    D,      67,     2/8

    E,      69,     1/8

    F,       99,     3/8

    G,      12,     3/8

    H,       9,      2/8

];

FINAL_TABLE:

LOAD

*,

    if([F2] = peek([F2]), peek(Rank),RecNo()) as Rank

Resident SAMPLE_DATA Order By F2 Desc;

Drop Table SAMPLE_DATA;

Result:

Capturar.PNG

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
sunny_talwar

May be this

Table:

LOAD * INLINE [

    F1, F2, Date

    A, 100, 1/8

    B, 90, 1/8

    C, 150, 2/8

    D, 67, 2/8

    E, 69, 1/8

    F, 99, 3/8

    G, 12, 3/8

    H, 9, 2/8

];


FinalTable:

LOAD *,

If(Date = Previous(Date), RangeSum(Peek('Rank'), 1), 1) as Rank

Resident Table

Order By Date, F2 desc;


DROP Table Table;

jonathandienst
Partner - Champion III
Partner - Champion III

You can also use Autonumber():

T_DATA:

LOAD * INLINE

[

  F1,  F2   Date

  A,   100  1/8

  B,   90   1/8

  ...

];

DATA:

LOAD F1,

    F2,

    Date,

    AutoNumber(Date, F1&F2) as Rank

Resident T_DATA

Order By F1, F2, Date DESC;

DROP TABLE T_DATA;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Thank you so much!!

Anonymous
Not applicable
Author

Great idea!

Thank you!

Anonymous
Not applicable
Author

Thank you for responding!

But, You're missing one of my requirement, which is it should be filtered by F2 and Date.