Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
agustapia
Contributor III
Contributor III

How to filter by several data ranges specified in a table

Hi all,

I have been thinking a lot and researching a lot and just can´t find an answer.

So I have a table with custom date ranges that are identified by the year and month that these ranges include most:

YearID       MonthID      Start Date     End Date

2018          01               2018-01-01     2018-01-28

2018          02               2018-01-29     2018-02-25

2018          03               2018-02-26     2018-04-01

...

And I need to give my users a filter by YearID and MonthID that sums the sales within the specific date range.

Unfortunately the date table and the sales table are in different data bases, so I cannot try an SQL query that adds an additional column with an identifier like YearID*100+MonthID for each range, which was my first thought.

Any help will be greatly appreciated.

1 Solution

Accepted Solutions
agustapia
Contributor III
Contributor III
Author

So the answer to my question came from these steps:

1. Open the first connection and load date ranges (periods)

2. Open the second connection and create a table with all the dates of each sale (sales).

3. Create another table like this:

OUTER JOIN

INTERVALMATCH(sales_date)

LOAD start_date, end_date

RESIDENT periods

This way YearID and MonthID are related to sales_date and I can use in a normal filter pane YearID and MonthID to filter by period.

It is important the order of the data load because the resident table is in memory and can be recalled even if its original connection source is closed. If we did the data load upside down there would be an error for Qlik not finding a field.

I am writing my own answer to thank everybody who spends their time to help newbies like me for free and just in case somebody else ever has the same issue I faced.

View solution in original post

1 Reply
agustapia
Contributor III
Contributor III
Author

So the answer to my question came from these steps:

1. Open the first connection and load date ranges (periods)

2. Open the second connection and create a table with all the dates of each sale (sales).

3. Create another table like this:

OUTER JOIN

INTERVALMATCH(sales_date)

LOAD start_date, end_date

RESIDENT periods

This way YearID and MonthID are related to sales_date and I can use in a normal filter pane YearID and MonthID to filter by period.

It is important the order of the data load because the resident table is in memory and can be recalled even if its original connection source is closed. If we did the data load upside down there would be an error for Qlik not finding a field.

I am writing my own answer to thank everybody who spends their time to help newbies like me for free and just in case somebody else ever has the same issue I faced.