Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Join

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Join

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.

13 Replies
amit_saini
Honored Contributor III

Re: Join

MVP
MVP

Re: Join

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.

Not applicable

Re: Join

Hi Jagan

Perfomance is very slow if am using Resident

MVP
MVP

Re: Join

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Join

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;

MVP
MVP

Re: Join

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;

Arjunarao
Honored Contributor II

Re: Join

Hi jagan

Nice explanation. Thank you.

Arjunarao
Honored Contributor II

Re: Join

Hi jagan mohan,

I am using this script but giving below error.

Please help me. Thanks in advance.

Capture.JPG

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

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

];

Community Browser