2 Replies Latest reply: Mar 20, 2013 3:47 AM by Sushil Kumar RSS

    Modifying base SQL code into QV script

      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
          Johannes Sunden

          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
            Sushil Kumar

            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

             

             

            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