Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

joakimwasseng
New Contributor III

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
joakimwasseng
New Contributor III

Re: Need help with converting SQL JOIN to QlikView LEFT JOIN

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;

5 Replies
Not applicable

Need help with converting SQL JOIN to QlikView LEFT JOIN

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;

joakimwasseng
New Contributor III

Need help with converting SQL JOIN to QlikView LEFT JOIN

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

Get back to you soon

//Joakim

joakimwasseng
New Contributor III

Need help with converting SQL JOIN to QlikView LEFT JOIN

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

Re: Need help with converting SQL JOIN to QlikView LEFT JOIN

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.

joakimwasseng
New Contributor III

Re: Need help with converting SQL JOIN to QlikView LEFT JOIN

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;

Community Browser