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.
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:
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;