Converting where statement of SQL to Qlik Script Editor

[melissa4]

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;

[MayilVahanan]

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

[QFabian · Accepted solution]

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