Max Date Value - Scripting Side

[paulwalker]

Hi Community,

I'm facing issue with Max date value  - we need fetch which has the maximum date value

ContactID	SeqNumber	Date	Sales
100123	1000	04/07/2019	5000
100123	1000	15/08/2019	5000
100123	1000	25/09/2019	2000
100123	1001	25/09/2019	2500

OutPut:

ContactID	SeqNumber	Date	Sales
100123	1000	25/09/2019	2000
100123	1001	25/09/2019	2500

Have used FirstSortedValue, but it's not working..

MaxValue:
Load * inline [
ContactID, SeqNumber, Date, Sales
100123, 1000, 04/07/2019, 5000
100123, 1000, 15/08/2019, 5000
100123, 1000, 25/09/2019, 2000
100123, 1001, 25/09/2019, 2500
];

JOIN
Load
         ContactID,
         SeqNumber,
         Date(Max(Date)) as Date,
         FirstSortedValue(Sales, -Date) as MaxValue
Resident MaxValue
Group By  ContactID,
                     SeqNumber;

Can you please help me!....

Thanks In Advance!!

[tresesco]

If you need just two rows as output, you don't need JOIN here. And one more thing you have to ensure that qlik is reading your date values as dates not string; for that set your system variable properly.

SET DateFormat='D/M/YYYY';

MaxValue:
Load * inline [
ContactID, SeqNumber, Date, Sales
100123, 1000, 04/07/2019, 5000
100123, 1000, 15/08/2019, 5000
100123, 1000, 25/09/2019, 2000
100123, 1001, 25/09/2019, 2500
];

/**** No JOIN***/

Final:
Load
         ContactID,
         SeqNumber,
         Date(Max(Date)) as Date,
         FirstSortedValue(Sales, -Date) as MaxValue
Resident MaxValue
Group By  ContactID,
                     SeqNumber;

Drop table MaxValue;

[Kushal_Chawda]

may be this

Data:
LOAD
    ContactID,
    SeqNumber,
    "Date",
    Sales
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

inner Join(Data)
Load ContactID,
    SeqNumber,
    Date(max(Date)) as Date
Resident Data
Group by ContactID,
    SeqNumber;

[Brett_Bleess]

Paul, did either of the posts get you what you needed for your use case?  If so, please do not forget to return to your thead and on the post that helped you, use the Accept as Solution button to mark that as the solution, which gives credit to the poster and lets other Members now what actually worked.  We appreciate it if you can close out the thread.  If you are still needing help, leave an update on where you stand with things.

Regards,
Brett