Identify the latest month+Year and match with ceratin date

[Theo_Westseit]

Hello Guys,

the following questions are regarding the script (not frontend).

So i have the following table :

Month.Year

12.2022

01.2023

11.2022

02.2023

How do i identify the latest / youngest period+Year, which would be 02.2023?
Based on the identified/youngest period+Year, i want to identify the latest date from 02.2023 from a different table and return a value.

The table nr.2 looks like this:

Date (DD/MM/YYYY)	Value
02.03.2023	1,5
01.03.2023	1,9
28.02.2023	1,4
27.02.2023	1,8

The latest date from 02.2023 is 28.02.2023, so the value to be returned should be 1,4.
Can someone provide any help on how the code should look like?

Thanks in advance!

[sidhiq91]

@Theo_Westseit  Please see the code below:

NoConcatenate
Temp:
Load Date(Date#(Month.Year,'MM.YYYY'),'MM/YYYY') as [Month Year]
inline [
Month.Year
12.2022
01.2023
11.2022
02.2023
];

NoConcatenate
Temp1:
Load Date(Max([Month Year]),'MM/YYYY') as MaxMonthYear
Resident Temp
;

Let VmaxMonthYear = Peek('MaxMonthYear',0,'Temp1');

NoConcatenate
Temp2:
Load
Date(Date#(Datefield,'DD.MM.YYYY'),'MM/DD/YYYY') as Max_Month_Value,
Date(Date#(Datefield,'DD.MM.YYYY'),'MM/YYYY') as MonthDate,
Value
Inline [
Datefield|Value
02.03.2023| 1,5
01.03.2023| 1,9
28.02.2023| 1,4
27.02.2023| 1,8
](delimiter is '|');

NoConcatenate
Temp3:
Load *
Resident Temp2
where MonthDate='$(VmaxMonthYear)';

inner join (Temp2)
Temp4:
Load Date(Max(Max_Month_Value),'MM/DD/YYYY') as Max_Month_Value

Resident Temp3;

Drop table Temp3, Temp, Temp1;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

[WildmoserGeorg · Accepted solution]

is this the desired result?

Script:

a:
load * inline [
MonthYear
12.2022
01.2023
11.2022
02.2023
];

b:
load * inline [
Date,Value
02.03.2023,1.5
01.03.2023,1.9
28.02.2023,1.4
27.02.2023,1.8
];

maxa:
load
date(max(date#(MonthYear,'MM.YYYY'))) as MaxaMonthStart,
monthend(date(max(date#(MonthYear,'MM.YYYY')))) as MaxaMonthEnd
resident a;

Inner Join IntervalMatch ( Date )
LOAD MaxaMonthStart, MaxaMonthEnd
Resident maxa;

maxb:
noconcatenate load
date(max(Date)) as maxdate
resident maxa;

left join (maxb) load Date as maxdate, Value resident b;

[Theo_Westseit]

Thank you for your help buddy!

[Theo_Westseit]

Your are AMAZING!!! Thanks so much 😁😁