topic Re: Join by two fields in QlikView

Join by two fields

vchuprina — Wed, 15 Jul 2015 17:36:49 GMT

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.

Re: Join by two fields

Wed, 15 Jul 2015 17:43:48 GMT

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

Re: Join by two fields

vchuprina — Wed, 15 Jul 2015 17:49:20 GMT

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

Re: Join by two fields

Wed, 15 Jul 2015 18:02:53 GMT

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

Re: Join by two fields

vchuprina — Wed, 15 Jul 2015 19:53:12 GMT

Typical filename :

NMK #5 JULY WK 2.xls