topic subtract dates in QlikView

subtract dates

Aldemarprins4 — Thu, 21 Jul 2022 18:16:02 GMT

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.

Re: subtract dates

sidhiq91 — Fri, 22 Jul 2022 02:53:28 GMT

@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.

Re: subtract dates

Aldemarprins4 — Fri, 22 Jul 2022 12:56:16 GMT

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

Re: subtract dates

hic — Fri, 22 Jul 2022 13:25:23 GMT

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

Re: subtract dates

Aldemarprins4 — Fri, 22 Jul 2022 13:30:35 GMT

Thank you