topic Re: Join in QlikView

Join

Fri, 03 Jul 2015 07:21:28 GMT

I have 2 Tables:

Table1:

Name

Table2:

Country

On doing a left join of table2 with table1

I need to create a composite key with ID, name. Country

Regards

Re: Join

amit_saini — Fri, 03 Jul 2015 07:25:38 GMT

Hi,

Check :

https://community.qlik.com/message/500622#500622

Thanks,
AS

Re: Join

jagan — Fri, 03 Jul 2015 07:32:09 GMT

Hi,

Try like this

Temp:

LOAD

FROM Table1;

Left Join(Temp)

LOAD

FROM Table2;

Data:

LOAD

AutoNumber(ID & '_' & name & '_' & Country) AS Key,

RESIDENT Temp;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

Re: Join

Fri, 03 Jul 2015 07:32:49 GMT

Hi Jagan

Perfomance is very slow if am using Resident

Re: Join

jonathandienst — Fri, 03 Jul 2015 07:33:53 GMT

You can do this via some joins:

Table1:

LOAD

ID,

Name

FROM...

Left Join (Table1)

LOAD

ID,

Country

FROM...

Left Join (Table1)

LOAD Distinct ID,

Name,

Country

ID & Name & Country As CompKey

Resident Table1;

Or via a mapping table and preceding load:

MapCountry:

Mapping LOAD

Country

FROM Table2...

Table1:

LOAD *,

ID & Name & Country As CompKey

LOAD

ID,

Name,

ApplyMap('MapCountry', ID) As Country

FROM Table1...

This may be more efficient as there is one less load.

Re: Join

MayilVahanan — Fri, 03 Jul 2015 07:35:23 GMT

Try like this

Table1:

Load

Name

From table1;

Join(Table1)

Load

Country

From table2;

Final:

Load *, AutoNumber(ID & '_' & Name & '_' & Country) AS %Key from Table1;

Drop table Table1;

Re: Join

jagan — Fri, 03 Jul 2015 07:40:13 GMT

Hi Senthil,

Try like this using Mapping Load

MapCountry:

Mapping LOAD

ID,

Country

FROM Table2;

Data:

LOAD *,

ID & Name & Country As Key;

LOAD

ID,

Name,

ApplyMap('MapCountry', ID) As Country

FROM Table1;

Re: Join

qlikviewwizard — Mon, 06 Jul 2015 06:14:28 GMT

Hi jagan

Nice explanation. Thank you.

Re: Join

qlikviewwizard — Mon, 06 Jul 2015 06:31:59 GMT

Hi jagan mohan,

I am using this script but giving below error.

Please help me. Thanks in advance.

MapCountry:

Mapping LOAD

[ID,Country

1,INDIA

2,Singapore

3,US

4,UK

5,Japan

];

Data:

LOAD *,

ApplyMap('MapCountry', ID) As Country,

ID & Name & Country As Key;

LOAD * inline [

ID,Name

1,A

2,B

3,C

4,D

5,E

6,F

7,G

8,H

9,I

10,J

];

EXIT Script;

Re: Join

MayilVahanan — Mon, 06 Jul 2015 06:56:26 GMT

Try like this

MapCountry:

Mapping LOAD

[ID,Country

1,INDIA

2,Singapore

3,US

4,UK

5,Japan

];

Data:

LOAD *, ID & Name & Country As Key;

LOAD *, ApplyMap('MapCountry', ID, Null()) As Country inline [

ID,Name

1,A

2,B

3,C

4,D

5,E

6,F

7,G

8,H

9,I

10,J

];

Re: Join

jagan — Mon, 06 Jul 2015 07:03:32 GMT

Hi,

USe this script

MapCountry:

Mapping LOAD

ID,

Country

FROM Table2;

Data:

LOAD *,

ID & Name & Country As Key;

LOAD

ID,

Name,

ApplyMap('MapCountry', ID) As Country

FROM Table1;

Regards,

Jagan.

Re: Join

qlikviewwizard — Mon, 06 Jul 2015 07:09:04 GMT

Hi MayilVahanan

Still I am getting the error.Please advise.

Re: Join

MayilVahanan — Mon, 06 Jul 2015 07:11:48 GMT

Please find attachment,

MapCountry:

Mapping LOAD * Inline

[

ID,Country

1,INDIA

2,Singapore

3,US

4,UK

5,Japan

];

Data:

LOAD *, ID & Name & Country As Key;

LOAD *, ApplyMap('MapCountry', ID, Null()) As Country inline [

ID,Name

1,A

2,B

3,C

4,D

5,E

6,F

7,G

8,H

9,I

10,J

];

Re: Join

qlikviewwizard — Mon, 06 Jul 2015 07:21:01 GMT

Hi MayilVahanan,

Please answer here. I will close this issue. Thank you.

Composite Key Issue with mapping Load.