topic Re: How to rank based on date in load script? in App Development

How to rank based on date in load script?

Anonymous — Mon, 20 Aug 2018 07:52:58 GMT

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

stalwar1‌ swuehl‌ Qlik Sense App Development & Usage‌ Qlik Sense Forums‌

Re: How to rank based on date in load script?

dwforest — Mon, 20 Aug 2018 20:13:39 GMT

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

Re: How to rank based on date in load script?

Anonymous — Tue, 21 Aug 2018 00:58:31 GMT

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

Thank you in advance!

Re: How to rank based on date in load script?

Thiago_Justen_ — Tue, 21 Aug 2018 11:25:59 GMT

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:

Re: How to rank based on date in load script?

sunny_talwar — Tue, 21 Aug 2018 11:31:09 GMT

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;

Re: How to rank based on date in load script?

jonathandienst — Tue, 21 Aug 2018 11:34:01 GMT

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;

Re: How to rank based on date in load script?

Anonymous — Thu, 23 Aug 2018 01:21:05 GMT

Thank you so much!!

Re: How to rank based on date in load script?

Anonymous — Thu, 23 Aug 2018 01:21:43 GMT

Great idea!

Thank you!

Re: How to rank based on date in load script?

Anonymous — Thu, 23 Aug 2018 01:23:43 GMT

Thank you for responding!

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