topic Re: Max date value in scripting in QlikView

Max date value in scripting

paulwalker — Sat, 16 Nov 2024 01:28:03 GMT

Hi Community,

I Have to show Max Value records ..

Table:

ContactID	SeqNumber	Date	Sales
100123	1000	4/7/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

MaxValue:
Load * inline [
ContactID, SeqNumber, Date, Sales
100123, 1000, 4/7/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;

I have written firstsortedvalue, but value not coming proper..

What did i wrong here, can you please help me!

Thanks In Advance!

Re: Max date value in scripting

Taoufiq_Zarra — Tue, 21 Jan 2020 15:23:33 GMT

from what I get that you're taking the Max date as a reference, I'll propose this:

Data: LOAD RowNo() as IDl,* INLINE [ ContactID, SeqNumber, Date, Sales 100123, 1000, 4/7/2019, 5000 100123, 1000, 15/08/2019, 5000 100123, 1000, 25/09/2019, 2000 100123, 1001, 25/09/2019, 2500 ]; inner join load FirstSortedValue(IDl, -Date) as IDl resident Data group by ContactID,SeqNumber;

output:

Re: Max date value in scripting

Brett_Bleess — Tue, 18 Feb 2020 16:44:38 GMT

Did Taoufiq's response help you get things working? If so, do not forget to return to your thread and use the Accept as Solution button to give him credit for the assistance and let other Members know it did work. We really appreciate it when folks close out their threads, so if you did something different, please consider posting that and then you can use the button to mark that post as the solution too. If you are still trying to sort things out, please leave an update with what you still need.

Regards,
Brett