Help to get the records based on latest priority date?

[durgabhavani]

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 ID	Priority	Date (M/DD/YYYY)
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

Expected Output:

Emp ID	Priority	Date (M/DD/YYYY)
301	Medium	6/12/2017
302	Medium	4/20/2017
305	High	2/13/2017
306	Medium	5/28/2017
307	High	5/18/2017

Thanks,

Durga

[Chanty4u]

try this

firstsortedvalue(Priority,-orderDate)

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Scripting/AggregationFunctions/f...

[sunny_talwar]

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

[durgabhavani]

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]

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 ID	Priority	Date (M/DD/YYYY)
301	High	6/12/2017
301	Medium	6/12/2017
302	Medium	4/20/2017
305	High	2/13/2017
306	High	5/28/2017
306	Low	5/28/2017
306	Medium	5/28/2017
307	High	5/18/2017
307	Low	5/18/2017

[sunny_talwar]

Are you looking to get this?

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]

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

Emp ID	Priority	Date (M/DD/YYYY)
301	High	6/12/2017
301	Medium	6/12/2017
302	Medium	4/20/2017
305	High	2/13/2017
306	High	5/28/2017
306	Low	5/28/2017
306	Medium	5/28/2017
307	High	5/18/2017
307	Low	5/18/2017

[sunny_talwar · Accepted solution]

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;

[durgabhavani]

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