Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a SQL statement that I need to convert to Qlik Sense Script Editor and am unable to get it to work, particularly where to put the Where statement for multiple joins. Below is the SQL statement and further down is an attempt that didn't work starting at the RESIDENT line.
SQL Statement
Select *
From AL1, AL2, AL3
Where AL1.Key = AL2.Key
and AL1.ID = AL2.ID
and AL1.Type = AL3.Type
and AL1.Business_Unit = ’A’
and AL2.Fiscal_Year = ‘2020’
and AL3.Project = ‘abc’
How to convert to Qlik Sense?
InitialTable:
Load *
FROM [lib://FINANCE_QVDs_WISDM/AL1.QVD]
(qvd);
Load *
FROM [lib://FINANCE_QVDs_WISDM/AL2.QVD]
(qvd);
Load *
FROM [lib://FINANCE_QVDs_WISDM/AL3.QVD]
(qvd);
FinalTable:
Load *
WHERE Business_Unit = ’A’
and Fiscal_Year = ‘2020’
and Project = ‘abc’
RESIDENT InitialTable;
DROP TABLE InitialTable;
Hi, try loading and joining tables one by one, starting from tables with "Where", be carefull with key fields :
original :
Select *
From AL1, AL2, AL3
Where AL1.Key = AL2.Key
and AL1.ID = AL2.ID
and AL1.Type = AL3.Type
and AL1.Business_Unit = ’A’
and AL2.Fiscal_Year = ‘2020’
and AL3.Project = ‘abc’
--->
example of qlik transformation :
Load * from AL3 where Project = ‘abc’ ;
left join
Load * from AL2 where Fiscal_Year = ‘2020’ ;
left join
Load * from AL1 where Business_Unit = ’A’;
HI @melissa4
You can use directly SQL query in Qliksense and store in the qvd.
Or
InitialTable:
Load *
FROM [lib://FINANCE_QVDs_WISDM/AL1.QVD]
(qvd);
Inner join(InitialTable)
Load *
FROM [lib://FINANCE_QVDs_WISDM/AL2.QVD]
(qvd);
Inner join(InitialTable)
Load *
FROM [lib://FINANCE_QVDs_WISDM/AL3.QVD]
(qvd);
Noconcatenate
Load * resident InitialTable where Business_Unit = ’A’
and Fiscal_Year = ‘2020’
and Project = ‘abc’;
Drop table InitialTable;
Hope join key between tables are below and there is no other common keys between tables.
AL1.Key = AL2.Key and AL1.ID = AL2.ID
AL1.Type = AL3.Type
Hi, try loading and joining tables one by one, starting from tables with "Where", be carefull with key fields :
original :
Select *
From AL1, AL2, AL3
Where AL1.Key = AL2.Key
and AL1.ID = AL2.ID
and AL1.Type = AL3.Type
and AL1.Business_Unit = ’A’
and AL2.Fiscal_Year = ‘2020’
and AL3.Project = ‘abc’
--->
example of qlik transformation :
Load * from AL3 where Project = ‘abc’ ;
left join
Load * from AL2 where Fiscal_Year = ‘2020’ ;
left join
Load * from AL1 where Business_Unit = ’A’;