Rank dates within a record

[spetushi]

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

[swuehl · Accepted solution]

LOAD ID,

        SeqNr,

         Date,

         Autonumber(Date, ID) as Rank

RESIDENT YourTable

ORDER BY ID, Date asc;

DROP TABLE YourTable;

[pra_kale]

Hi,

Please check this..

A:

LOAD ID,

     SeqNr,

     Date

FROM

(ooxml, embedded labels, table is Sheet1);

NoConcatenate

B:

LOAD *,

AutoNumber(Date,ID) as Rank

Resident A Order by Date asc;

DROP Table A;

EXIT Script

[sunny_talwar]

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;

[Anonymous]

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

[swuehl]

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

[sunny_talwar]

That make sense

[spetushi]

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;

OR

FinalTable:

LOAD *,

   Autonumber(SeqNr, ID) as Rank

RESIDENT TempTable

ORDER BY ID, SeqNr asc;

DROP TABLE TempTable;

[Saravanan_Desingh]

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.

[sunny_talwar]

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?