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: 
michael_anywar
Creator
Creator

Join with 3 tables

I need some help. I have 3 tables in my editor. and loaded. as seen below. Now i want to create a join table with Specific fields. Somebody guide newbie.

Thanks.

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

RestConnectorMasterTable:

SQL SELECT

"Field1",

"Field2",

"Field3",

"Field4",

FROM JSON (wrap on) "root"

WITH CONNECTION();

NEXT startAt;

[Table1]:

LOAD

"Field1"AS "A",

"Field2"AS "B",

"Field3"AS "C",

"Field4" AS "D"

RESIDENT RestConnectorMasterTable;

//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

RestConnectorMasterTable:

SQL SELECT

"Field1",

"Field2",

"Field3",

"Field4",

FROM JSON (wrap on) "root2"

WITH CONNECTION();

NEXT startAt;

[Table2]:

LOAD

"Field1" AS "A",

"Field2"AS "B",

"Field4" AS "C"

RESIDENT RestConnectorMasterTable;


//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

RestConnectorMasterTable:

SQL SELECT

"Field1",

"Field2",

"Field3",

"Field4",

FROM JSON (wrap on) "root3"

WITH CONNECTION();

NEXT startAt;

[Table3]:

LOAD

"Field1" AS "A",

"Field2"  AS "B",

"Field4"AS "C",

RESIDENT RestConnectorMasterTable;

//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


So The ResidentMaster Tables of  Table1 has a common Field with the ResidentMaster Tables of both Table2 and Table3.

I would want to create a Joint table with specific fields though.

Below is my current statement. Can i write the Join statement after loading all the tables..? or.....somewhere in the middle. And would it be correct..?

.................................................

....................................................

Activities_join:

NoConcatenate

Load

,

,

, Common Field

Resident Table1;


Left Join (Table2)

Load

A],Common field

,

Common Field

Resident Table2;

Left Join(Table3)

NoConcatenate

Load

A],Common Field

Resident Table3;

DROP Table2;

DROP Table3;

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

Hi,

here after you first load your third table (table3) after the last resident, you will have a model like this

PS: don't forget to drop the RestConnectorMasterTable table(s) !

model.png

then you can concatenate your table and use flags, you will have something like this:

model2.png

View solution in original post

2 Replies
YoussefBelloum
Champion
Champion

Hi,

here after you first load your third table (table3) after the last resident, you will have a model like this

PS: don't forget to drop the RestConnectorMasterTable table(s) !

model.png

then you can concatenate your table and use flags, you will have something like this:

model2.png

bramkn
Partner - Specialist
Partner - Specialist

Depending on what data you want to keep you can just join the tables to eachother when ever you want. so for example you can:

RestConnectorMasterTable:

SQL SELECT

"Field1",

"Field2",

"Field3",

"Field4",

FROM JSON (wrap on) "root2"

WITH CONNECTION();

NEXT startAt;

LEFT JOIN (Table1)

LOAD

"Field1" AS "A",

"Field2"AS "B",

"Field4" AS "C"

RESIDENT RestConnectorMasterTable;