Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vchuprina
New Contributor II

Join by two fields

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:

Untitled.jpg

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.

1 Solution

Accepted Solutions
Not applicable

Re: Join by two fields

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

4 Replies
Not applicable

Re: Join by two fields

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

vchuprina
New Contributor II

Re: Join by two fields

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

Not applicable

Re: Join by two fields

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

vchuprina
New Contributor II

Re: Join by two fields

Typical filename :

NMK #5 JULY WK 2.xls

Community Browser