Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
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
balrajahlawat
Esteemed Contributor

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

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

11 Replies
balrajahlawat
Esteemed Contributor

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

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

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

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.

balrajahlawat
Esteemed Contributor

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

Not applicable

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

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

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

"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

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

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

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

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 😐

balrajahlawat
Esteemed Contributor

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

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;

balrajahlawat
Esteemed Contributor

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

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