topic Max Date Value - Scripting Side in QlikView

Max Date Value - Scripting Side

paulwalker — Sat, 16 Nov 2024 01:27:35 GMT

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!!

Re: Max Date Value - Scripting Side

tresB — Tue, 21 Jan 2020 12:17:56 GMT

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;

Re: Max Date Value - Scripting Side

Kushal_Chawda — Tue, 21 Jan 2020 12:25:40 GMT

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;

Re: Max Date Value - Scripting Side

Brett_Bleess — Wed, 19 Feb 2020 17:17:24 GMT

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