Qlik left join to the same table - how it works in SQL?

Question

I have such a tranformation in Qlik

abc:
SELECT distinct
AA.ACCOUNT_ID AS ACCOUNT_ID_AA,
AA.ID AS ID_AA,
AA.F_a AS F_a_AA,
AA.F_b AS F_b_AA,
AA.F_c AS F_c_AA,
AA.F_d AS F_d_AA,
AA.F_e AS F_e_AA,
AA.F_f AS F_f_AA,
AA.F_g AS F_g_AA,
AA.P_A_ID AS P_A_ID_AA,
AA.U_P_A_ID AS U_P_A_ID_AA,
AA.A_C AS A_C_AA,
AA.R AS R_AA,
AA.F_h__C AS F_h__C_AA,
AA.I AS I_AA,
AA.L AS L_AA
FROM $(vdb).A AA
;
left join (abc)
LOAD distinct
ACCOUNT_ID_AA AS P_ACCOUNT_ID_AA,
F_a_AA AS P_a_AA,
F_b_AA AS P_b_AA,
F_c_AA AS P_c_AA,
F_d_AA AS P_d_AA,
F_e_AA AS P_e_AA,
F_f_AA AS P_f_AA,
F_g_AA AS P_g_AA
Resident abc;
left join (abc)
LOAD distinct
ACCOUNT_ID_AA AS U_P_ACCOUNT_ID_AA,
F_a_AA AS U_P_a_AA,
F_b_AA AS U_P_b_AA,
F_c_AA AS U_P_c_AA,
F_d_AA AS U_P_d_AA,
F_e_AA AS U_P_e_AA,
F_f_AA AS U_P_f_AA,
F_g_AA AS U_P_g_AA
Resident abc;

Could you explain me what is the purpose of it? To handle relation between some hierarchies?

The second question is qlik joining. How does it join it? Could someone please show me this example on PostgreSQL or any other SQL language? The only thing I know is that Qlik joins by columns with the same name. So it means that SQL join is going to have in "ON" section all the column names?

I can't figure the logic behind this out.

Answer 1

The confusing part is that there is no "normal" join in the above script. And by "normal" i mean that there are no common fields to join on. And thats the way to create cross join in Qlik - just join two tables without common fields and Qlik will create many-to-many relations.

If i simplify the script and load account and one more field:

abc:
Load * Inline [
ACCOUNT_ID_AA, F_a_AA
1 , ABC
2 , DEF
3 , GHI
];
left join (abc)
LOAD
 ACCOUNT_ID_AA as P_ACCOUNT_ID_AA,
 F_a_AA as P_a_AA
Resident
 abc
;
left join (abc)
LOAD
 ACCOUNT_ID_AA as U_P_ACCOUNT_ID_AA,
 F_a_AA as U_P_a_AA
Resident
 abc
;

Then when select one account id the result will be:

table result

You are right. Qlik will automatically join tables based on common fields (case sensitive). For example (psudo code):

TableName:
Select
 AccountID,
 CustomerID
 TransactionDate
From
 Some_DB_Table
;
left join (TableName)
// TableName is optional here
// if not specified Qlik will assume
// that the table above is the one to join to
Select
 AccountID,
 CustomerID
 OrderDate
From
 Another_DB_Table
;

Will be equal to the following SQL:

SELECT s.AccountID AS AccountID,
 s.CustomerID AS CustomerID,
 s.TransactionDate AS TransactionDate,
 c.OrderDate AS OrderDate
FROM Some_DB_Table s
 LEFT OUTER JOIN Another_DB_Table a
 ON s.AccountID = a.AccountID
 AND s.CustomerID = c.CustomerID;

• Dawid_K: You skipped distinct, but yea I think its more clear then it was. So basically, with distinct keywords, if I have 1 milion of records in the base table, after these two joins the amount of records will be a million to the third power?
• Stefan Stoychev: Correct. Not sure whats the reason behind this script but probably makes sense in the context of the requirements. (sorry about missing the distinct)