Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
melissa4
Contributor II
Contributor II

Converting where statement of SQL to Qlik Script Editor

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;

Labels (5)
1 Solution

Accepted Solutions
QFabian
Specialist III
Specialist III

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

QFabian

View solution in original post

2 Replies
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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
QFabian
Specialist III
Specialist III

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

QFabian