Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

11 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

Not applicable
Author

oops, forgot to mention the data i have.

The data that i have is like thus:

Column1Column2
item1200
item1400
item2250

Column1Column3
item1A broadsword with bloodstain
item2A huge axe that hungers for blood
Not applicable
Author

"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.

Not applicable
Author

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

Column1Column2
item1200
item1400
item2250

Table 2:

Column1Column3
item1A broadsword with bloodstain
item2A huge axe that hungers for blood
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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