Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vchuprina
Specialist
Specialist

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.

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

4 Replies
Not applicable

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
Specialist
Specialist
Author

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

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Not applicable

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
Specialist
Specialist
Author

Typical filename :

NMK #5 JULY WK 2.xls

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").