topic Re: Prefix code to calendar days in QlikView

Prefix code to calendar days

tamilarasu — Mon, 15 Dec 2014 04:38:24 GMT

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.

Re: Prefix code to calendar days

jagan — Mon, 15 Dec 2014 05:29:14 GMT

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.

Re: Prefix code to calendar days

Mon, 15 Dec 2014 05:34:51 GMT

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

Re: Prefix code to calendar days

tamilarasu — Mon, 15 Dec 2014 05:41:25 GMT

Great. Thank you Jagan.

Re: Prefix code to calendar days

tamilarasu — Mon, 15 Dec 2014 05:42:42 GMT

Ram, figured it out. Thank you.

Re: Prefix code to calendar days

tamilarasu — Mon, 15 Dec 2014 07:17:05 GMT

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.

Re: Prefix code to calendar days

anbu1984 — Mon, 15 Dec 2014 07:30:13 GMT

I am able to add SalesOrgDate as dimension. Check this

Re: Prefix code to calendar days

tamilarasu — Mon, 15 Dec 2014 09:08:14 GMT

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

Re: Prefix code to calendar days

jagan — Mon, 15 Dec 2014 09:13:02 GMT

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.

Re: Prefix code to calendar days

tamilarasu — Mon, 15 Dec 2014 09:24:32 GMT

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.

Re: Prefix code to calendar days

jagan — Mon, 15 Dec 2014 09:27:05 GMT

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.

Re: Prefix code to calendar days

anbu1984 — Mon, 15 Dec 2014 09:29:11 GMT

Final_Calendar:

Load

Country,FKDAT,

Country &''& FKDAT as SalesOrgDate

Resident Country;

Re: Prefix code to calendar days

tamilarasu — Mon, 15 Dec 2014 09:35:05 GMT

Hi Jagan. Working fine now. Thank you.

Re: Prefix code to calendar days

tamilarasu — Mon, 15 Dec 2014 09:38:22 GMT

Thank you all.