Get Max(valid_from) if valid_from <= today

[reporting_neu]

Hello,
I have a price list with a “valid from” date.
I always want to load the current price in the load script.

How can I do that? I thought maybe something similar to what was written in the title.

Can you help me?

Price	valid from
10,00	01.01.2024
15,00	15.03.2024
12,00	01.04.2024
18,00	01.05.2024

[Clement15]

Hello,
here is an example of what you can do. You can use the TAG in the set analysis to have the right calculations

Test:
Load *
Inline [
Price,valid_from
10,01/01/2024
15,15/03/2024
12,01/04/2024
18,01/05/2024

];

left join

Load
max(valid_from) as valid_from,
1 as TAG
Resident Test;

[reporting_neu]

Thanks for your answer. Unfortunately, with yours, Qlik would also give me a future date.

I think it's best to do it like this:

TMP1:
Load
*
Resident Table
Where valid_from <= today();

left join

Load
Max(valid_from) as Max_Date,
1 as TAG
Resident TMP1;

I think that's the way how it would be working.

[Clement15 · Accepted solution]

If there are future dates it is actually preferable to use this method. It depends on your willingness to keep all the prizes but you can use this method to avoid a step

Test:
Load *
Inline [
Price,valid_from
10,01/01/2024
15,15/03/2024
12,01/04/2024
18,01/05/2024

];

left join

Load
max(valid_from) as valid_from,
1 as TAG
Resident Test
where valid_from <= Today();