Search code examples
sqljoinleft-joinqlikviewqliksense

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

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.

Solution

  • 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;