Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Fq
Partner - Contributor III
Partner - Contributor III

Filter dates based on interval defined

Hi everyone,

 

I have id's of files my company is supposed to receive . Each file has its own id. Then there is another field called "expected_date", that contains for each file, all the expected dates the file is supposed to be recieved.

But then, there is another table that contains the id of each file, and also two date fields, "valid from" and "valid to" dates, those dates specify an interval where the file is supposed to be recieved.

The thing is that "Expected dates" from the other table contains more dates than the interval, so I have to filter them.

Here I attach a simple example: with the original table, the table with the intervals and the desired table. 

Fq_0-1672594412225.png

Basically I need a table with all the expected dates, that exist within the interval defined by each File ID.

 

I am thinking about using Interval Match, but is not working:

 

Dates_Expected_By_File:
LOAD
ID,
Expected_Date 

RESIDENT ....;

ID_Interval:
Load

ValidFromDate,
ValidToDate,
ID

RESIDENT ...;

 


INNER JOIN INTERVALMATCH(Expected_Date)
LOAD

ValidFromDate,
ValidToDate,
ID

RESIDENT ID_Interval;

After this, I wanted to delete all the records that doesn't have a linked interval (ValidFrom and ValidTo). 

 

The error says: Interval fields list not matching number of fields....

 

I would appreciate some help,

Kind regards.

1 Reply
Oliver_F
Partner - Creator III
Partner - Creator III

Hi,

i don't know this error, but it sounds like the field formats don't line up. I guess that your date is loaded as a string instead of a number / date.

Please check in the data model viewer if the field is a date. And try to use date#() or date(date#()) or num(date#()) on the three date fields to make it numerical value.