Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have created a 3 years calendar table by using load script and total of 1096 days. Now, I want to add 20 country codes to each date. I am not sure how to achieve this as I am new to Qlikview.
For Ex:
Likewise I need to prefix the 20 country code to all 1096 days.
Thanks in advance.
Hi,
Try this script
Temp:
LOAD
*
INLINE [
Country
India
USA
UK];
JOIN
LOAD
*
INLINE [
Date
1/1/2014
1/2/2014
1/3/2014];
Data:
LOAD
*,
Country & Date AS Key
RESIDENT Temp;
DROP TABLE Temp;
Hope this helps you.
Regards,
jagan.
HI,
Try like this
Country:
LOAD
CountryCode
FROM Country;
JOIN
LOAD
*
FROM Calendar;
This will become cartesian product or cross join between the two tables, since there is no common column in both the tables.
Hope this helps you.
Regards,
Jagan.
after Jagan's code you can derive the new column Final Data by concatenating using another load statement.
Great. Thank you Jagan.
Ram, figured it out. Thank you.
Hi Jagan,
I got a problem. After adding the country, I just tried to concatenate country with the date but my system getting hanged. When I tried to add SalesOrgDate as dimension the problem occurred.
Please see attached file.
I am able to add SalesOrgDate as dimension. Check this
I want to concatenate all country code to single date like wise 1096 date entries. I followed jagan solution but there is no common column, so for single date entry I got 27400 rows.
So the system getting hanged due to large concatenation. Please refer above attachment sent by Anbu chelian.
Any idea!!
Hi,
Did you check the Calendar table, how many rows in that table? If you have 1096 then for each country you will get 1096 rows.
Check this sample script;
Data:
LOAD
*
INLINE [
Country
India
USA
UK];
JOIN
LOAD
*
INLINE [
Date
1/1/2014
1/2/2014
1/3/2014];
After executing this I am getting exactly 9 rows, 3 countries * 3 dates = 9
Hope this helps you.
Regards,
jagan.
Yes, I have 1096 rows. I have no issues while adding country column but have issue while concatenating the date with country .
Please refer below screenshot and try to add SalesOrgDate to dimension field. Hope you understand my problem.