topic Re: Concatenate 3 tables with Left join in QlikView

Concatenate 3 tables with Left join

tango — Fri, 17 Feb 2023 11:55:59 GMT

Hi,

I have 3 tables (B,C,D)which needs to be joined to a another table (A) in such a way that A should be joined to B by Left Join and accordingly C & D with A.

Now what I have tried is ,creating a left join and each section like separate table and simultaneously in next 2 sections for C & D. its creating synthetic key ,I am not sure how should I concatenate them , because Concatenate between 2 tables gives me Full Join which i do not want.

Please help.

Thanks

Re: Concatenate 3 tables with Left join

BrunPierre — Fri, 17 Feb 2023 16:17:51 GMT

Can you post some sample data.

Re: Concatenate 3 tables with Left join

edwin — Fri, 17 Feb 2023 21:19:59 GMT

did you drop the 3 tables you joined to table A? since you left joined all 3 tables to a fourth, you would have duplicated all teh fields fro tables BCD - this could have caused the syth tables.

Re: Concatenate 3 tables with Left join

edwin — Fri, 17 Feb 2023 21:20:53 GMT

also concatenate is not a join - 2 different things

Re: Concatenate 3 tables with Left join

tango — Sat, 18 Feb 2023 11:48:03 GMT

Sure..

Table:
load * Inline [

X,Y,Z

1,a,a1

2,b,b2

3,c,c3

];

left join(Table)
Table1:
load * Inline [

X,Y,Y

1,a,aa1

2,b,bb2

3,c,cc3

];

left join(Table)
Table2:
load * Inline [
X,Y,D

1,a,aaa

2,b,bbb

3,c,ccc

];

left join(Table)
Table3:
load * Inline [
X,Y,P

1,a,12

2,b,23

3,c,34

];

How can I have this in 1 table (Table) above ,because I want to create some calculated fields too.

Re: Concatenate 3 tables with Left join

tango — Sat, 18 Feb 2023 12:34:54 GMT

This is actually my requirement ,so if I am loading Main table thrice it doesnt mean anything , if I load it once and then perform left join and then drop and resident Table , it shows data for 1st loaded table and no data for another

Re: Concatenate 3 tables with Left join

tango — Sat, 18 Feb 2023 12:35:21 GMT

Yes I tried with that , when I drop it I do not see any details of it

Re: Concatenate 3 tables with Left join

BrunPierre — Sat, 18 Feb 2023 13:47:35 GMT

Hi @tango

Base on the diagram, what will the final table look like?

Re: Concatenate 3 tables with Left join

edwin — Mon, 20 Feb 2023 15:42:48 GMT

you dont need to join. Qlik will handle the association. there must be a reason why you want to join them. if you want to convert the DM to a star schema then yes, join them and then drop the source tables. if you arent joining all the fields, you drop the fields you joined into the first table. but that goes back to the question why do it at all.

left join (MainTable) load ID, Date, //these are your keys i suppose Title //this gets added to main table resident Table1; //at this point Title is in main table drop Table1; // you dont need this anymore as //youve loaded table1 info into Main //do the same to the other tables

Re: Concatenate 3 tables with Left join

tango — Tue, 21 Feb 2023 19:30:13 GMT

Can we have some structure like we can resident Maintable and create a New
Source table wherein I can left joined data from another tables and create
Calculated fields.

Re: Concatenate 3 tables with Left join

BrunPierre — Wed, 22 Feb 2023 08:18:03 GMT

I'm not sure what you're trying to accomplish, but perhaps this?

MainTable:
LOAD * FROM MainTable;

Noconcatenate
NewSourceTable:
LOAD ID,
Name,
Date

Resident MainTable;
DROP Table MainTable;

Left Join(NewSourceTable)
LOAD ID,
Date,
Title
FROM Table1;

Left Join(NewSourceTable)
LOAD ID,
Date,
Address
FROM Table2;

Left Join(NewSourceTable)
LOAD ID,
Date,
Country
FROM Table3;

Noconcatenate
FinalTable:
LOAD ID,
Name,
Title,
Date,
Address,
Title &' '& Name as [Name Title], //Calculated field
Country &'-'& Address as [Residential Address] //Calculated field

Resident NewSourceTable;
DROP Table NewSourceTable;

Re: Concatenate 3 tables with Left join

tango — Wed, 22 Feb 2023 13:38:57 GMT

This Left join doesn't gives me correct number of ID because its counting the ID from another table when performing left join in 2nd part. 1st table left join throws correct distinct ID but then 2nd table left join is throwing ID (considering some ID from 1st table too).

Re: Concatenate 3 tables with Left join

edwin — Wed, 22 Feb 2023 16:15:40 GMT

you should drop Table1, Table2, and Table3. perhaps the issue you are encountering is due to synthetic tables.

Re: Concatenate 3 tables with Left join

tango — Thu, 23 Feb 2023 13:48:12 GMT

this means

Maintable:

Load ID, NAME;

table1:

Left Join(Maintable)

Load Title

resident Maintable;

table2:

Left Join(Maintable)

Load Country

resident Maintable;

Now where should I drop table1 and table2 as they are joined with MainTable.

Sorry for being so at beginner level. but getting confused.

Re: Concatenate 3 tables with Left join

BrunPierre — Thu, 23 Feb 2023 14:01:13 GMT

Can you share some actual data sample and desired output, maybe there are other ways to achieve the same results.

Re: Concatenate 3 tables with Left join

edwin — Thu, 23 Feb 2023 23:57:40 GMT

it sounds like you really dont have table1, table2. from your code you are actually trying to load table1 and table2 from the data in Maintable.

i dont think you are using the right statements. from the looks of it, your source table is Maintable. i suggest you add the following before you start your left joins:

exit script;

then take a screenshot of your data model. this will help everyone understand your baseline tables. then show us what you want to achieve.