Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I loaded NFR_MONTH_SHORT_NAME and NRF_WK_OF_MONTH from file name and then try to join it with NRFcalendar file. NRF calendar file contains following fields:
WKDate:
LOAD
Month(Date#('$(MonthName2)','MMMM'))AS NFR_MONTH_SHORT_NAME,
SubField(SubField(FileName(), ' ',-1 ),'.',1) AS NRF_WK_OF_MONTH
FROM
[$(vFile)]
$(vLEXT);
Left Join
LOAD WKEND,
NRF_MONTH_SHORT_NAME,
NRF_WK_OF_MONTH,
FISCAL_YEAR
FROM
(ooxml, embedded labels, table is Sheet1)
WHERE FISCAL_YEAR = '2015';
How can I return WKEND date from file.
Is below script line giving expected result? Verify that before joining the table with Fiscal Calendar.
SubField(SubField(FileName(), ' ',-1 ),'.',1) AS NRF_WK_OF_MONTH
Couple of issues I see here are
1. SubField retuns a string. Convert it to Number.
2. As per QlikView help, If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated. If this is causing the issue, do the second SubField in the Load on Load statement.
And provide your sample file name/file name format
Regards,
KKR
Couldn't understand your requirement. After the left join, the resultant table will have WKEND. What do you mean by 'How can I return WKEND date from file'?
Regards,
KKR
It didn't return correct WKEND, for example
If in filename
NRF_MONTH_SHORT_NAME = Mar and
NRF_WK_OF_MONTH = 3 it should return 3/21/2015, but it return 2/7/2015 and its happened for different month and week. It always return first line - 2/7/2015
Is below script line giving expected result? Verify that before joining the table with Fiscal Calendar.
SubField(SubField(FileName(), ' ',-1 ),'.',1) AS NRF_WK_OF_MONTH
Couple of issues I see here are
1. SubField retuns a string. Convert it to Number.
2. As per QlikView help, If several subfield functions are used in the same load statement, the Cartesian product of all combinations will be generated. If this is causing the issue, do the second SubField in the Load on Load statement.
And provide your sample file name/file name format
Regards,
KKR
Typical filename :
NMK #5 JULY WK 2.xls