Generate Max Date based on Two other fields

[Sai33]

Hello All,

I've the following Source Table

ID	LOC	Mark ID	Event Date
1	AA	30	01.05.2020
1	12	35	01.06.2020
1	12	0	03.06.2020
2	AB	50	01.05.2020
2	DC	55	20.05.2020

Based on LOC and Mark ID fields i would like to generate a New Column Called Last Date.

If LOC is a Numeric Value and Mark ID is 0 than Event Date else Today() as Last Date. So, my expected result is as follows

ID	Last Date
1	03.06.2020
2	06.06.2020

Thanks

Sai

[Taoufiq_Zarra]

@Sai33 

whay it's not :

for ID=1 we have two cases, one with 0 and one without? How to choose?

[Taoufiq_Zarra]

unless you mean Max Date, this is the script :

Data:

LOAD *,date#([Event Date1],'DD.MM.YYYY') as [Event Date]  INLINE [
    ID, LOC, Mark ID, Event Date1
    1, AA, 30,01.05.2020
    1, 12, 35,01.06.2020
    1, 12, 0,03.06.2020
    2, AB, 50,01.05.2020
    2, DC, 55,20.05.2020
];

output:

load ID,
if(FirstSortedValue(LOC,-[Event Date])*FirstSortedValue([Mark ID],-[Event Date])=0,FirstSortedValue([Event Date],-[Event Date]),date(today(),'DD.MM.YYYY')) as [Last Date] resident Data group by ID;

drop table Data

output :

attached qvf file

[Sai33]

Hello Toufiq,

Thanks for your quick input.

Your Solution here, almost works but i cannot use the FirstSortedValue function as there might be more incoming data later.

I have updated the Source Data and Expected Solution in the attached QVF file.

Thanks and Regards

Sai

[Taoufiq_Zarra]

Hi Sai,

attached a new version

[Sai33]

Hi Taoufiq,

Your Script is somehow not working with my Real data.

I have updated the QVF file can you please have a look.

[Taoufiq_Zarra · Accepted solution]

 I hope this version corresponds to your requirement

[Sai33]

Thank you Taoufiq