Data load editor join tables

[wanyunyang]

Hi guys,

I have two tables: A and B in data load editor, with a same field: CUSTOMER, in which way will QLIK SENSE automatically join this two tables on CUSTOMER field (INNER, OUTER, LEFT, RIGHT)?  

Thanks in advance!

[OmarBenSalem]

full outer join

[wanyunyang]

Thanks!

If my two tables are in two sections in data load editor, and I want to do A left join B, what should I do?

[jerifortune · Accepted solution]

You can explicitly specify the joining table name. For example

tableB:
Load customerID, name
From <Source>;

Left Join (tableB)

tableA:

Load customerID, joiningDate

From <Source>;

Hope this helps

[OmarBenSalem]

to control the data joins you'd have to do that via the script

sthing like this:

table1:

load * from source1;

left join(table1)

load * from source2;

the join would be based on the common field btwn the 2 tables.

please refer to :

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/combine-tables...

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

https://www.resultdata.com/using-joins-in-qlikview/

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/combine-tables...

[wanyunyang]

I did something like (script already simplified):

table:

SELECT customer.No_ AS Customer_No, customer.Name AS Customer

FROM customer;

LEFT JOIN (table)

SELECT s."Customer No_" AS Customer_No, s.Amount AS sales_Amount

FROM sales s;

LOAD * Resident table;

When I create a pivot table with dimension Customer_No and measure SUM(sales_Amount), every SUM result is doubled. I checked my SQL query in DBeaver, the result is correct. Why is it doubled from data load editor?

[jerifortune]

I suspect you have  duplicate records  in your customer table. You can comment on other tables in your data model and remove or comment on LEFT JOIN (table). The two tables will be associated using the Customer_No. Try your pivot table again.

[wanyunyang]

This is what I get after commenting:

The strange thing is when I do Count(Amount) directly in pivot table column 2, the result duplicates. But if I do COUNT(Amount) GROUP BY... in the script, result shown in column 3, I get the right answer. Any idea?

[wanyunyang]

Actually in column 2, everything shows 2, not duplicated.

[jerifortune]

Could you share a sample qvf  file?