Prefix code to calendar days

[tamilarasu]

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.

[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.

[#3]

after Jagan's code you can derive the new column Final Data by concatenating using another load statement.

[tamilarasu]

Great. Thank you Jagan.

[tamilarasu]

Ram, figured it out. Thank you.

[tamilarasu]

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.

[anbu1984]

I am able to add SalesOrgDate as dimension. Check this

[tamilarasu]

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!!

[jagan]

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.

[tamilarasu]

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.