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: 
Anonymous
Not applicable

Need help with converting SQL JOIN to QlikView LEFT JOIN

I'm looking for help with converting this SQL-syntax to QlikView? I'm not sure how to create a JOIN-statement for table oru --> oh---> salj

SELECT

oru.artnr 

,salj.koststallekod    

FROM oru

JOIN oh ON

oru.foretagkod = oh.foretagkod

AND oru.ordernr = oh.ordernr

JOIN salj ON

oh.foretagkod = salj.foretagkod

AND UPPER(oh.saljare) = UPPER(salj.saljare)

WHERE oru.ordlevdat >= '20110101'

Thanks //Joakim

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Thanks Alex, you put me in the right direction!

Created som "common fileds" as with key values.

I successfully joined 3 tables!

oru:
LOAD
foretagkod & '#' & ordernr AS KEY_oru_oh

,ordlevdat AS datum_date

,artnr


FROM $(QvdRelativeOriginalFolder)oru.qvd (qvd);
LEFT JOIN
LOAD
foretagkod & '#' & ordernr AS KEY_oru_oh
,foretagkod & '#' & UPPER(saljare) AS KEY_oh_salj

FROM $(QvdRelativeOriginalFolder)oh.qvd (qvd);
LEFT JOIN
LOAD
foretagkod & '#' & UPPER(saljare) AS KEY_oh_salj
,koststallekod
FROM $(QvdRelativeOriginalFolder)salj.qvd (qvd);

DROP FIELD KEY_oru_oh, KEY_oh_salj;

View solution in original post

5 Replies
Not applicable
Author

Hi,

try something like this

SELECT

oru.artnr 

,salj.koststallekod  

,foretagkod

,ordernr

FROM oru;

LEFT JOIN

Select

foretagkod,ordernr,

UPPER(saljare) as saljare

FROM oh;

LEFT JOIN

SELECT foretagkod,

UPPER(saljare) as saljare

FROM salj

WHERE ordlevdat >= '20110101':

drop field foretagkod,ordernr,saljare;

Anonymous
Not applicable
Author

Thanks Alex...I'll try it out!

Get back to you soon

//Joakim

Anonymous
Not applicable
Author

Get cannot find field on "oru.artnr" and "salj.artnr"

Tried work with the script you sent but without luck....

Sorry, need more help

Not applicable
Author

Try:

SELECT

artnr 

,foretagkod

,ordernr

FROM oru;

LEFT JOIN

Select

foretagkod,ordernr,

UPPER(saljare) as saljare

FROM oh;

LEFT JOIN

SELECT foretagkod,

koststallekod,

UPPER(saljare) as saljare

FROM salj

WHERE ordlevdat >= '20110101':

drop field foretagkod,ordernr,saljare;

The join is made over all the common fields.

Anonymous
Not applicable
Author

Thanks Alex, you put me in the right direction!

Created som "common fileds" as with key values.

I successfully joined 3 tables!

oru:
LOAD
foretagkod & '#' & ordernr AS KEY_oru_oh

,ordlevdat AS datum_date

,artnr


FROM $(QvdRelativeOriginalFolder)oru.qvd (qvd);
LEFT JOIN
LOAD
foretagkod & '#' & ordernr AS KEY_oru_oh
,foretagkod & '#' & UPPER(saljare) AS KEY_oh_salj

FROM $(QvdRelativeOriginalFolder)oh.qvd (qvd);
LEFT JOIN
LOAD
foretagkod & '#' & UPPER(saljare) AS KEY_oh_salj
,koststallekod
FROM $(QvdRelativeOriginalFolder)salj.qvd (qvd);

DROP FIELD KEY_oru_oh, KEY_oh_salj;