script to join/merge table in the data model viewer. need help :(

[#1]

Hi there  Qlikers, anyone knows how to from this

to this (below) ?

1st scenario load script is :

table1:

LOAD

    Column1,

    Column2

FROM [lib://testConnection (rsmain2_administrator)/testDATA.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

table2:

LOAD

    Column1,

    Column3

FROM [lib://testConnection (rsmain2_administrator)/testData2.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

2nd scenario load script is :

table1:

LOAD

    Column1,

    Column2

FROM [lib://testConnection (rsmain2_administrator)/testDATA.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

inner join

LOAD

    Column1,

    Column3

FROM [lib://testConnection (rsmain2_administrator)/testData2.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

I have tried:

table3:

load Column1, Column2 Resident table1;

inner join

load Column1, Column3 Resident table2;

drop table table1;

drop table table2;

But it results in "NO DATA LOADED". Any knows how?

[Anonymous]

Does Column1 have some common records b/w them?

Say, I am having

Load

Column1,    //Column1 have value 2 & 3.

Column2

From TableName;

inner join

Load

Column1,    //Column1 have value 1 & 4.

Column2

From TableName;

Now In this case It would not give you any data as nothing is common in Column1 for both tables

[#3]

Hi,

As Balraj told there may not be any common data between column1 of table1 and column1 of table2, as inner join functionality is to return only common data between two tables.

In that case you can use "Outer Join" ,but this may lead to nulls in your data.

Regards,

Reshma.

[Anonymous]

You can go through these as well for better understanding?

Understanding Join, Keep and Concatenate

How to join two tables in QlikSense or where do we put SQL?

[#5]

oops, forgot to mention the data i have.

The data that i have is like thus:

Column1	Column2
item1	200
item1	400
item2	250

Column1	Column3
item1	A broadsword with bloodstain
item2	A huge axe that hungers for blood

[#6]

"Understanding Join, Keep and Concatenate‌" only mentioned joining of the tables while loading, but my current issue is joining tables that are already loaded. I have gone through this several times. It doesn't mention how to join those tables that are already loaded unfortunately.

Any ideas how Balraj?

I had tried this:
"

table3:

load Column1, Column2 Resident table1;

inner join

load Column1, Column3 Resident table2;

drop table table1;

drop table table2;

" but am without luck.

[#7]

as a matter of fact, there is a common data. Apologize for not mentioning.
Table 1:

Column1	Column2
item1	200
item1	400
item2	250

Table 2:

Column1	Column3
item1	A broadsword with bloodstain
item2	A huge axe that hungers for blood

[#8]

Hey,

You are not getting any data from the 1st scenario you mentioned?

Is the last script lines common for both your first and second scenarios?

Can you post exact script which was not working?

As per the data you specified "inner join" works perfectly fine for me 😐

[Anonymous]

I think your script is like this?

tab1:

load * Inline

[Column1, Col2

Item1, 200

Item2, 300

];

tab2:

load * Inline

[Column1, Col3

Item1, xsad

Item3, dgft

];

NoConcatenate     //Dont forget this to mention b/w resident and your load table as it will automatically concat your data

load * Resident tab1;

Inner Join

LOAD * Resident tab2;

drop Table tab1;

DROP Table tab2;

[Anonymous · Accepted solution]

Replace this with your old script?

table1:

LOAD

    Column1,

    Column2

FROM [lib://testConnection (rsmain2_administrator)/testDATA.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

inner join

LOAD

    Column1,

    Column3

FROM [lib://testConnection (rsmain2_administrator)/testData2.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

noconcatenate

//I have tried:

table3:

load Column1, Column2 Resident table1;

inner join

load Column1, Column3 Resident table2;

drop table table1;

drop table table2;

Now try above script, it should work