topic Need help with converting SQL JOIN to QlikView LEFT JOIN in QlikView

Need help with converting SQL JOIN to QlikView LEFT JOIN

Anonymous — Fri, 27 May 2011 12:59:25 GMT

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

Need help with converting SQL JOIN to QlikView LEFT JOIN

Fri, 27 May 2011 13:27:09 GMT

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;

Need help with converting SQL JOIN to QlikView LEFT JOIN

Anonymous — Fri, 27 May 2011 13:29:04 GMT

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

Get back to you soon

//Joakim

Need help with converting SQL JOIN to QlikView LEFT JOIN

Anonymous — Fri, 27 May 2011 14:13:02 GMT

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

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

Sorry, need more help

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

Fri, 27 May 2011 14:34:42 GMT

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.

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

Anonymous — Fri, 27 May 2011 15:04:20 GMT

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;