topic Re: Rank dates within a record in QlikView

Rank dates within a record

spetushi — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am loading a table in qlikview similar to this sample:

ID	SeqNr	Date
1	11	05-Sep-2015
1	28	07-Oct-2015
1	14	26-Sep-2015
2	18	13-Sep-2015
2	21	15-Sep-2015
2	22	25-Sep-2015
2	27	05-Oct-2015
3	32	15-Oct-2015
4	92	21-Nov-2015
4	95	25-Nov-2015

Would like to rank the dates within each ID from oldest to earliest. Tried different concepts using grouping and while loop, but couldn't get it to work. Desired result:

ID	SeqNr	Date	Rank
1	11	05-Sep-2015	1
1	28	07-Oct-2015	3
1	14	26-Sep-2015	2
2	18	13-Sep-2015	1
2	21	15-Sep-2015	2
2	22	25-Sep-2015	3
2	27	05-Oct-2015	4
3	32	15-Oct-2015	1
4	92	21-Nov-2015	1
4	95	25-Nov-2015	2

Thank you

Re: Rank dates within a record

swuehl — Sun, 10 Apr 2016 12:05:31 GMT

LOAD ID,

SeqNr,

Date,

Autonumber(Date, ID) as Rank

RESIDENT YourTable

ORDER BY ID, Date asc;

DROP TABLE YourTable;

Re: Rank dates within a record

pra_kale — Sun, 10 Apr 2016 12:13:46 GMT

Hi,

Please check this..

LOAD ID,

SeqNr,

Date

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

LOAD *,

AutoNumber(Date,ID) as Rank

Resident A Order by Date asc;

DROP Table A;

EXIT Script

Re: Rank dates within a record

sunny_talwar — Sun, 10 Apr 2016 12:41:10 GMT

Another option could be to use Peek() function:

Table:

LOAD ID,

SeqNr,

Date

FROM

[https://community.qlik.com/thread/212687]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

If(Peek('ID') = ID, RangeSum(Peek('Rank'), 1), 1) as Rank

Resident Table

Order By ID, Date;

DROP Table Table;

Re: Rank dates within a record

Anonymous — Sun, 10 Apr 2016 12:44:48 GMT

That's quite cunning. Never thought of using AutoNumber() like that.

Re: Rank dates within a record

swuehl — Sun, 10 Apr 2016 12:45:38 GMT

Yes, might be also an option. Note that there is a difference between the two options if ID's SeqNr share a Date.

Re: Rank dates within a record

sunny_talwar — Sun, 10 Apr 2016 12:47:48 GMT

That make sense

Re: Rank dates within a record

spetushi — Sun, 10 Apr 2016 14:33:24 GMT

Thank you Sunny, Swuehl, and Pra, for your responses.

With a minor modification to address the scenario where ID-SeqNr have same date, all your suggestions work.

Here is what it looks like:

FinalTable:

LOAD *,

If(Peek('ID') = ID, RangeSum(Peek('Rank'), 1), 1) as Rank

Resident TempTable

Order By ID, SeqNr asc;

DROP Table TempTable;

FinalTable:

LOAD *,

Autonumber(SeqNr, ID) as Rank

RESIDENT TempTable

ORDER BY ID, SeqNr asc;

DROP TABLE TempTable;

Re: Rank dates within a record

Saravanan_Desingh — Mon, 11 Apr 2016 19:31:55 GMT

Hi swuehl‌,

I have tried your code. But the AutoNumber generates Rank only on the input LOAD order and not taking the ORDER BY. Am I missing something here? Can you please advise? Thank you.

Re: Rank dates within a record

sunny_talwar — Mon, 11 Apr 2016 19:39:02 GMT

Are you sure it isn't taking Order by into account? I think it is:

Script1:

Data:

LOAD * INLINE [

ID, SeqNr, Date

1, 11, 05-Sep-2015

1, 28, 07-Oct-2015

1, 14, 26-Sep-2015

2, 18, 13-Sep-2015

2, 21, 15-Sep-2015

2, 22, 25-Sep-2015

2, 27, 05-Oct-2015

3, 32, 15-Oct-2015

4, 92, 21-Nov-2015

4, 95, 25-Nov-2015

];

DataOut:

LOAD ID,

SeqNr,

Date,

Autonumber(Date, ID) as Rank

RESIDENT Data

ORDER BY ID, Date asc;

DROP TABLE Data;

Script2:

Data:

LOAD * INLINE [

ID, SeqNr, Date

1, 11, 05-Sep-2015

1, 28, 07-Oct-2015

1, 14, 26-Sep-2015

2, 18, 13-Sep-2015

2, 21, 15-Sep-2015

2, 22, 25-Sep-2015

2, 27, 05-Oct-2015

3, 32, 15-Oct-2015

4, 92, 21-Nov-2015

4, 95, 25-Nov-2015

];

DataOut:

LOAD ID,

SeqNr,

Date,

Autonumber(Date, ID) as Rank

RESIDENT Data

ORDER BY ID, Date desc;

DROP TABLE Data;

The only change between the two scripts in the Order By for Date field. Are you not seeing these changes on your end?

Re: Rank dates within a record

Saravanan_Desingh — Mon, 11 Apr 2016 20:00:12 GMT

Hi Sunny,

I'm getting exactly what you are getting.

In your first picture: Shouldn't the Rank be : 1,3,2? (5/Sep,7/Oct,26/Sep)

Re: Rank dates within a record

sunny_talwar — Mon, 11 Apr 2016 20:11:11 GMT

I see what you mean

Re: Rank dates within a record

sunny_talwar — Mon, 11 Apr 2016 20:13:56 GMT

The issue was that the date wasn't read by QlikView.

Check now:

Data:

LOAD ID,

SeqNr,

Date#(Date, 'DD-MMM-YYYY') as Date;

LOAD * INLINE [

ID, SeqNr, Date

1, 11, 05-Sep-2015

1, 28, 07-Oct-2015

1, 14, 26-Sep-2015

2, 18, 13-Sep-2015

2, 21, 15-Sep-2015

2, 22, 25-Sep-2015

2, 27, 05-Oct-2015

3, 32, 15-Oct-2015

4, 92, 21-Nov-2015

4, 95, 25-Nov-2015

];

DataOut:

LOAD ID,

SeqNr,

Date,

Autonumber(Date, ID) as Rank

RESIDENT Data

ORDER BY ID, Date asc;

DROP TABLE Data;

Re: Rank dates within a record

Saravanan_Desingh — Mon, 11 Apr 2016 20:17:09 GMT

Thank you sunindia. Perfected now..

Re: Rank dates within a record

Valerie — Wed, 11 Sep 2019 08:35:13 GMT

Thank, autonumber function works but it takes quiet long to run the script, probably because of the size of my data. Any alternative?