Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
durgabhavani
Creator III
Creator III

Help to get the records based on latest priority date?

Hi All,

Please find the sample data below and expected output. Help to get the records based on the latest priority date.

Sample Data:

   

Emp IDPriorityDate (M/DD/YYYY)
301High5/30/2017
301Medium6/12/2017
302Medium4/20/2017
305High2/13/2017
306High3/30/2017
306Low4/14/2017
306Medium5/28/2017
307High1/23/2017
307High

5/18/2017

Expected Output:

   

Emp IDPriorityDate (M/DD/YYYY)
301Medium6/12/2017
302Medium4/20/2017
305High2/13/2017
306Medium5/28/2017
307High5/18/2017

Thanks,

Durga

1 Solution

Accepted Solutions
sunny_talwar

That is a weird request, but okay.... check this

Table:

LOAD * INLINE [

    Emp ID, Priority, Date

    301, High, 5/30/2017

    301, Medium, 6/12/2017

    302, Medium, 4/20/2017

    305, High, 2/13/2017

    306, High, 3/30/2017

    306, Low, 4/14/2017

    306, Medium, 5/28/2017

    307, High, 1/23/2017

    307, High, 5/18/2017

];

Left Join (Table)

LOAD [Emp ID],

Date(Max(Date)) as Max_Date

Resident Table

Group By [Emp ID];

DROP Field Date;

RENAME Field Max_Date to Date;


Capture.PNG

View solution in original post

10 Replies
sunny_talwar

You want this in the script or on a front end chart?

durgabhavani
Creator III
Creator III
Author

in script.

sunny_talwar

Try this

Table:

LOAD * INLINE [

    Emp ID, Priority, Date

    301, High, 5/30/2017

    301, Medium, 6/12/2017

    302, Medium, 4/20/2017

    305, High, 2/13/2017

    306, High, 3/30/2017

    306, Low, 4/14/2017

    306, Medium, 5/28/2017

    307, High, 1/23/2017

    307, High, 5/18/2017

];

Right Join (Table)

LOAD [Emp ID],

Max(Date) as Date

Resident Table

Group By [Emp ID];

durgabhavani
Creator III
Creator III
Author

Thank sunny.It is working perfect.

I have another case. If i dont want to decrease the records and need to assign the max date value to that records how to handle it. Please find expected output.

   

Emp IDPriorityDate (M/DD/YYYY)
301High6/12/2017
301Medium6/12/2017
302Medium4/20/2017
305High2/13/2017
306High5/28/2017
306Low5/28/2017
306Medium5/28/2017
307High5/18/2017
307Low5/18/2017
sunny_talwar

Are you looking to get this?

Capture.PNG

Try this

Table:

LOAD * INLINE [

    Emp ID, Priority, Date

    301, High, 5/30/2017

    301, Medium, 6/12/2017

    302, Medium, 4/20/2017

    305, High, 2/13/2017

    306, High, 3/30/2017

    306, Low, 4/14/2017

    306, Medium, 5/28/2017

    307, High, 1/23/2017

    307, High, 5/18/2017

];

Left Join (Table)

LOAD [Emp ID],

FirstSortedValue(Priority, -Date) as Max_Priority

Resident Table

Group By [Emp ID];

durgabhavani
Creator III
Creator III
Author

I am looking to get max date to all records per profile id as shown in below table. ( observe date field).

   

Emp IDPriorityDate (M/DD/YYYY)
301High6/12/2017
301Medium6/12/2017
302Medium4/20/2017
305High2/13/2017
306High5/28/2017
306Low5/28/2017
306Medium5/28/2017
307High5/18/2017
307Low5/18/2017
sunny_talwar

That is a weird request, but okay.... check this

Table:

LOAD * INLINE [

    Emp ID, Priority, Date

    301, High, 5/30/2017

    301, Medium, 6/12/2017

    302, Medium, 4/20/2017

    305, High, 2/13/2017

    306, High, 3/30/2017

    306, Low, 4/14/2017

    306, Medium, 5/28/2017

    307, High, 1/23/2017

    307, High, 5/18/2017

];

Left Join (Table)

LOAD [Emp ID],

Date(Max(Date)) as Max_Date

Resident Table

Group By [Emp ID];

DROP Field Date;

RENAME Field Max_Date to Date;


Capture.PNG

durgabhavani
Creator III
Creator III
Author

Perfect. Sorted out my problem. if possible give me whatsapp number.