How to use Peek/Previous for multiple dimension values?

[Henk_The_Novice]

Dear all,

Can anyone help me out with a challenge?

I need the max date for each Name but with the condition that if that max date is higher than today I need the next date.

For each Name I need only 1 date in the output. 

Data is already sorted DESC

Input

Table1

Name,Date
A,2-9-2023
A,30-8-2023
A,25-1-2023
B,1-1-2023
B,5-5-2022

Expected Output

A,30-8-2023
B,1-1-2023

My idea was to use an expression like 

LOAD

distinct Name,

If(Peek(Date)>today(),previous(Date),Peek(Date)

Resident Table1

This didn't work. Curious for your thoughts.

Thanks in advance.

[Aurelien_Martinez · Accepted solution]

Hi,

You can try a sxript like this:

data:
Load
Name,
Date#(Date, 'D-M-YYYY') as Date
Inline [
Name,Date
A,2-9-2023
A,30-8-2023
A,25-1-2023
B,1-1-2023
B,5-5-2022
];

NoConcatenate
Load
Name,
Max(Date) as Date
Where
FLG_DATE = 1
Group By
Name
;
Load
Name,
Date,
If(Date <= Today(), 1, 0) as FLG_DATE
Resident data
;

Drop Table data;

[Henk_The_Novice]

Dear Aurelien,

Thanks for your reply. I copied the script and it is working. It took me some time to understand your logic but now it's clear. I was assuming that I needed the Peek/Previous but probably the lack of the DATE function it was not working. Thanks for your creative solution with the MAX function.

Regards