Max date value in scripting

[paulwalker]

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!

[Taoufiq_Zarra]

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:

[Brett_Bleess]

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