topic Re: Modifying base SQL code into QV script in QlikView

Modifying base SQL code into QV script

Tue, 19 Mar 2013 18:38:28 GMT

My coding abilities are quite limited and I am attempting to recreate a manual or
adhoc data pull into an automated fully functioning QV document. I was given
the below code which is correct logic from various system tables. I understand
using ODBC and connect statements and general load and from statements, but
joining the below tables according to the logic I was given has proven to be
difficult…

I have the below code that I am unable to recreate/modify for my qlikview
document to reload. I have two different data sets that I eventually need to
concatenate, but I am not to that point just yet.

If anyone can point me into the correct direction or explain how I need to change
this code please help.

Thanks!

Data Set#1:

SELECT DISTINCT CHWHSE, CHDIV,
CONCAT(TRIM(CHPCTL),TRIM(CHSTOR)) AS CHCUPO, CHPCTL, CHSVIA, SOSTCT, SOSTST,
LEFT(TRIM(SOSTZP),5) AS PHSHZP, CHCASN, CHTQTY, CHDLM

FROM PKMANH221D.CHCART00 CH INNER JOIN PKMANH221D.SOSTOR00
SO ON CHSTOR = SOSTOR

WHERE CHDIV = 'RTL' AND CHSTAT = '85'

Data Set#2:

SELECT DISTINCT PHWHSE, PHDIV, PHCUPO, PHPCTL, PHSVIA, PHSHCT,
PHSHST, LEFT(TRIM(PHSHZP),5) AS PHSHZP, CHCASN, CHTQTY, CHDLM

FROM ((PKMANH221D.PHPICK00 PH LEFT JOIN PKMANH221D.PDPICK00
PD ON PHWHSE = PDWHSE AND PHPCTL = PDPCTL) LEFT JOIN PKMANH221D.CHCART00 CH ON
PHPCTL = CHPCTL AND PHWHSE = CHWHSE) LEFT JOIN PKMANH221D.CDCART00 CD ON PHPCTL
= CDPCTL AND PHDIV = CDDIV

WHERE PHARAC = 'CARRIER' AND (PHPSTF BETWEEN 40 AND 90) AND
(PHDIV = 'DTC' OR PHDIV = 'RTL') AND CHSTAT = '85' AND CHSVIA = 'D56' AND
PDSTYL = CDSTYL

Re: Modifying base SQL code into QV script

Anonymous — Wed, 20 Mar 2013 07:20:36 GMT

Why don't you just run the queries as-is from the QlikView script and let the database do the joining? That way you'll just get the resulting sets into QlikView

Re: Modifying base SQL code into QV script

sushil353 — Wed, 20 Mar 2013 07:47:12 GMT

you can write in qlikveiw script like:

Tab1:

Load

CHWHSE,

CHDIV,

CHCUPO,

CHPCTL,

CHSVIA,

SOSTCT,

SOSTST,

PHSHZP,

CHCASN,

CHTQTY,

CHDLM

SQL SELECT DISTINCT CHWHSE, CHDIV,
CONCAT(TRIM(CHPCTL),TRIM(CHSTOR)) AS CHCUPO, CHPCTL, CHSVIA, SOSTCT, SOSTST,
LEFT(TRIM(SOSTZP),5) AS PHSHZP, CHCASN, CHTQTY, CHDLM

FROM PKMANH221D.CHCART00 CH INNER JOIN PKMANH221D.SOSTOR00

SO ON CHSTOR = SOSTOR

WHERE CHDIV = 'RTL' AND CHSTAT = '85'

Tab2:

Load

PHWHSE,

PHDIV,

PHCUPO,

PHPCTL,

PHSVIA,

PHSHCT,

PHSHST,

PHSHZP,

CHCASN,

CHTQTY,

CHDLM

SQL SELECT DISTINCT PHWHSE, PHDIV, PHCUPO, PHPCTL, PHSVIA, PHSHCT,
PHSHST, LEFT(TRIM(PHSHZP),5) AS PHSHZP, CHCASN, CHTQTY, CHDLM

WHERE PHARAC = 'CARRIER' AND (PHPSTF BETWEEN 40 AND 90) AND
(PHDIV = 'DTC' OR PHDIV = 'RTL') AND CHSTAT = '85' AND CHSVIA = 'D56' AND
PDSTYL = CDSTYL