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
