Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
Can you provide sample XL file data?
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 |
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];
Thanks! That worked and the FYDayNum that it returns is correct.
Yes. You can read little more here: