topic Re: How to filter by several data ranges specified in a table in App Development

How to filter by several data ranges specified in a table

agustapia — Mon, 10 Sep 2018 17:59:21 GMT

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.

Re: How to filter by several data ranges specified in a table

agustapia — Tue, 11 Sep 2018 18:09:09 GMT

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.