topic Re: dates intervalmatch with inline in App Development

dates intervalmatch with inline

charlie2 — Wed, 12 Jan 2022 13:15:24 GMT

Hi,

I have a table with sales on a daily basis and I would like in the data script to filter these by the last 365 days. I tried this with an interval match but somehow doesn't work and i can't really tell why.

Here my tables:

First table where I generate the interval:

Time:
LET vDatumMin = Date(today()-365);
LET vDatumMax = Date(today()-1);

LOAD * Inline [
DatumMin, DatumMax,
'$(vDatumMin)', '$(vDatumMax)'];

Second table with my sales:

Sales:

LOAD

Date(Datum) AS Datum,

Customer,

Materialcode

FROM XXX;

Here I try to do the intervalmatch

Left Join ([Sales]) IntervalMatch(Datum)
LOAD $(vDatumMin), $(vDatumMax)
Resident Time;
left Join ([Sales]) LOAD * RESIDENT Time;
DROP TABLE Time;

This is quite of a standard procedure but somehow the interval match is not set up. I end up later with all the sales instead of the last 365 days. I'm pretty sure it's a format-related problem but can't find it out.

Any help is more than appreciated

Thanks a lot!

Re: dates intervalmatch with inline

Andrei_Cusnir — Thu, 13 Jan 2022 10:09:56 GMT

Hello,

If my understanding is correct, you would like to load all the records that are not older than 365 days from today. To do so, you can go with a much simpler solution such as using the WHERE clause in the Data load editor script. Here are the steps that I have followed to achieve the same outcome:

1. I have created the following fake dataset:

Ignore the "Days" field and the red color, as I have added it to demonstrate which records we want to exclude from our dataset.

2. In the Data load editor I have added the following script:

MyData:
LOAD
ID,
"Date",
Amount,
"Days"
FROM [...]
(...)
WHERE Date > Date(AddYears(Today(), -1))
;

I have used the WHERE clause to load all the records where the Date is grater than the current date - 1 year. To achieve that, I have used the AddYears() [1] function and I have passed the values Today() and -1.

3. This is the outcome that I have:

As you can see, only the records where days count is less than 365 are loaded.

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members.

---

[1] https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFunctions/addyears.htm

Re: dates intervalmatch with inline

charlie2 — Thu, 13 Jan 2022 12:31:07 GMT

Hi @Andrei_Cusnir ,

Many thanks for your great reply. That's indeed what I was looking for. It works perfectly.

I actually had already tried a WHERE Clause but didn't figure out how to do it within a range, that's why i came up with the idea of an interval match, which is far more complex.

Thanks again for the support