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

[Saravanan_Desingh]

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)

[sunny_talwar]

I see what you mean

[sunny_talwar]

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;

[Saravanan_Desingh]

Thank you sunindia. Perfected now..

[Valerie]

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