Join

[#1]

Hi

I have 2 Tables:

Table1:

ID

Name

Table2:

ID

Country

On doing a left join of table2 with table1

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

Regards

[amit_saini]

Hi,

Check :

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

Thanks,
AS

[jagan · Accepted solution]

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.

[#4]

Hi Jagan

Perfomance is very slow if am using Resident

[jonathandienst]

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

  ID

  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.

[MayilVahanan]

Hi

Try like this

Table1:

Load

ID

Name

From table1;

Join(Table1)

Load

ID

Country

From table2;

Final:

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

Drop table Table1;

[jagan]

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;

[qlikviewwizard]

Hi jagan

Nice explanation. Thank you.

[qlikviewwizard]

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;

[MayilVahanan]

Hi

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

];