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: 
gauravgg
Partner - Creator
Partner - Creator

how to load max date row from the table ?

hi all

I have the following table

IDDateAmount
00101-Jan-20161000
00102-Feb-20162000
00101-Mar-20164000
00201-Aug-20166000
00201-Dec-20167000

From the above table i have to load only those records which has max date for each ID

the output should be as follows

IDDateAmount
00101-Mar-20164000
00201-Dec-20167000

can anyone help me in this ?

Thanks in advance

5 Replies
sunny_talwar

Try this script:

Table:

LOAD

    ID,

    "Date",

    Amount

FROM [lib://Web]

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

Right Join (Table)

LOAD ID,

  Max(Date) as Date

Resident Table

Group by ID;


Capture.PNG

sunny_talwar

You can also try this:

Table:

LOAD

    ID,

    "Date",

    Amount

FROM [lib://Web]

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

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where ID <> Previous(ID)

Order By ID, Date desc;

gauravgg
Partner - Creator
Partner - Creator
Author

thanks sunny

timrogers13
Contributor II
Contributor II

Thanks Sunny.  This helped me out. -Tim

kfloresc
Contributor II
Contributor II

thank you so much! you have no idea how much this helped, i've been trying to figure out something similar for days now!