subtract dates

[Aldemarprins4]

Hello friends, I have several files that contain information on rates and come with the names of the date that is created, example May 2022

Create a field
Date:
5/1/2022
6/1/2022
7/1/2022

I want to do the following:

Date                  End Date                 Money

5/1/2022          5/31/2022                 USD

5/1/2022          5/31/2022                 COP

6/1/2022          6/30/2022                 USD

6/1/2022          6/30/2022              COP

7/1/2022          TODAY()                 USD

7/1/2022        TODAY()                  COP

This is for the purpose of doing an intervalMatch

to calculate conversion rate

The End Date field must be updated each time a new file is created in order for intervalmatch to work.

Please, if you don't understand me, tell me and I'll explain again.

[sidhiq91]

@Aldemarprins4  See the below script if that was something that you are looking for:

NoConcatenate
CreateDate:
Load * Inline [
Date
5/1/2022
6/1/2022
7/1/2022
];

Temp:
NoConcatenate
Load
Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') as Date,
Monthend(Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY')) as [End Date],
'USD' as Money

Resident CreateDate;
Concatenate(Temp)
Load
Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY') as Date,
Monthend(Date(Date#(Date,'MM/DD/YYYY'),'MM/DD/YYYY')) as [End Date],
'COP' as Money
Resident CreateDate;

Drop table CreateDate;

Exit Script;

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

[Aldemarprins4]

Hello, thank you very much, but it doesn't work for me, because I need is that when the file that has the name of the date does not exist, Qliview leaves that END DATE in TODAY so that the interval continues calculating the old rate until a new file with a new rate

[hic · Accepted solution]

Take a look at https://community.qlik.com/t5/Qlik-Design-Blog/Creating-a-Date-Interval-from-a-Single-Date/ba-p/1467...

[Aldemarprins4]

Thank you