topic Re: Join tables based on date range in QlikView

Join tables based on date range

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I am new to Qlikview and am having trouble with this load script. Would greatly appreciate some help.

My master data source (Table1) comprises 113 columns but my focus is one field named DtOfSale. My fiscal year data source (Table2) comprises FYDayNum which is a number 0 through 12, and the corresponding LastBusDayMinus4 and LastBusDayPlus4 dates, as shown below:

FYDayNum	LastBusDayMinus4	LastBusDayPlus4
0	08/27/2018	09/06/2018
1	09/24/2018	10/04/2018
2	10/25/2018	11/06/2018
3	11/26/2018	12/06/2018
4	12/25/2018	01/04/2019
5	01/25/2019	02/06/2019
6	02/22/2019	03/06/2019
7	03/25/2019	04/04/2019
8	04/24/2019	05/06/2019
9	05/27/2019	06/06/2019
10	06/24/2019	07/04/2019
11	07/25/2019	08/06/2019
12	08/26/2019	09/05/2019

The results that I need are the FYDayNum value based on the range in which my field DtOfSale in Table 1 falls. For example, if DtOfSale is 9/26/2018 then the FYDayNum returned should be 1 because it falls within the LastBusDayMinus4 of 09/24/2018 and LastBusDayPlus4 of 10/04/2018..

Here is what I have so far:

[Table1]:

LOAD

DtOfSale

FROM

[..\..\..\..\MainData.xlsx]

(ooxml, embedded labels);

[Table2]:

LOAD

FYDayNum,

LastBusDayMinus4,

LastBusDayPlus4

FROM

[..\..\..\..\FiscalMapping.xlsx]

(ooxml, embedded labels, table is LastBusinessDayOfFY);

tabMatch:

IntervalMatch(dateid)

LOAD FYDayNum, DtOfSale

Re: Join tables based on date range

MK9885 — Mon, 06 Aug 2018 18:02:58 GMT

Can you provide sample XL file data?

Re: Join tables based on date range

Anonymous — Mon, 06 Aug 2018 18:19:28 GMT

Yes, below is sample data from the Table1 data source -- I just noticed that I put "dateid" in the my first post. The field is DtOfSale not dateid:

recordid	DtOfSale	sales	fiscal_quarter	fiscal_year
1	7/2/2018	192.9284	1	2018
2	7/2/2018	90.52667	3	2018
3	7/2/2018	1070.269	1	2018
4	7/2/2018	8927.081	4	2019
5	4/2/2018	1.18999	3	2018
6	4/2/2018	32	3	2018
7	4/2/2018	14.429	4	2018
8	4/2/2018	4.046853	3	2018
9	7/2/2018	11.61	2	2018
10	7/2/2018	519.5493	2	2018
11	7/2/2018	163.3998	2	2018
12	4/2/2018	46.06867	3	2018
13	4/2/2018	2833.333	4	2018
14	4/2/2018	5219.451	2	2019
15	10/1/2015	1532.859	1	2016
16	8/1/2017	8.18388	1	2018
17	10/1/2015	954.0403	3	2016
18	5/2/2016	12119.8	4	2017
19	11/3/2014	10000	4	2015
20	11/3/2014	10150	2	2015
21	10/1/2013	33.28341	1	2014
22	10/1/2013	666.6667	2	2014
23	7/2/2018	4.198333	3	2018
24	7/2/2018	504.3895	3	2018
25	7/2/2018	1.333333	4	2018
26	8/1/2016	3499.117	3	2017
27	7/1/2014	333.3333	4	2014

Re: Join tables based on date range

vishsaggi — Mon, 06 Aug 2018 18:32:41 GMT

May be try below:

[Table1]:

LOAD

DtOfSale

FROM

[..\..\..\..\MainData.xlsx]

(ooxml, embedded labels);

[Table2]:

LOAD

FYDayNum,

LastBusDayMinus4,

LastBusDayPlus4

FROM

[..\..\..\..\FiscalMapping.xlsx]

(ooxml, embedded labels, table is LastBusinessDayOfFY);

INNER JOIN

tabMatch:

IntervalMatch(DtOfSale)

LOAD LastBusDayMinus4, LastBusDayPlus4

Resident [Table2];

Re: Join tables based on date range

Anonymous — Mon, 06 Aug 2018 18:53:20 GMT

Thanks! That worked and the FYDayNum that it returns is correct.

Re: Join tables based on date range

vishsaggi — Mon, 06 Aug 2018 19:21:10 GMT

Yes. You can read little more here:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes/IntervalMatch.htm