1 Reply Latest reply: Dec 15, 2010 7:27 PM by Rob Wunderlich RSS

    Questions about SQL Server Query to Qlikview Query

      Hello,

      I'm newbie in Qlikview, and I'm trying to modelise an SQL Server query into a Qlikview Query.

      Here the SQL Server Query :

       

      SELECT "F090PARC"."F090KY", "F090PARC"."F090LIB", "F091IMMAT"."F091IMMA", "F470LD"."F470DTDEP", "F470LD"."F470DUREE", "F470LD"."F470DTARRP", "F050TIERS"."F050KY", "F050TIERS"."F050NOM", "F050TIERS_COMM"."F050PRENOM", "F050TIERS_COMM"."F050NOM", "F090PARC"."K090T07TYP", "F470LD"."F470DTARR", "F470LD"."F470DTAVEDEB", "F470LD"."F470DTAVEFIN", "F470LD"."K470T46TYP", "F050TIERS_COND"."F050NOM", "F050TIERS_COND"."F050PRENOM" FROM ((((("Alocpro"."dbo"."F090PARC" "F090PARC" LEFT OUTER JOIN "Alocpro"."dbo"."F570MVT" "F570MVT" ON "F090PARC"."F090KY"="F570MVT"."K570090UNI") LEFT OUTER JOIN "Alocpro"."dbo"."F091IMMAT" "F091IMMAT" ON ("F090PARC"."K090091IMM"="F091IMMAT"."F091KY") AND ("F090PARC"."F090KY"="F091IMMAT"."K091090UNI")) LEFT OUTER JOIN "Alocpro"."dbo"."F470LD" "F470LD" ON "F570MVT"."F570KY"="F470LD"."K470570MVT") LEFT OUTER JOIN "Alocpro"."dbo"."F050TIERS" "F050TIERS" ON "F470LD"."K470050TIE"="F050TIERS"."F050KY") LEFT OUTER JOIN "Alocpro"."dbo"."F050TIERS" "F050TIERS_COND" ON "F470LD"."K470050CON"="F050TIERS_COND"."F050KY") LEFT OUTER JOIN "Alocpro"."dbo"."F050TIERS" "F050TIERS_COMM" ON "F050TIERS"."K050050COM"="F050TIERS_COMM"."F050KY" WHERE "F090PARC"."F090KY"<>'9999999999' AND "F470LD"."F470DTDEP" IS NOT NULL AND "F470LD"."F470DTDEP"<{ts '2010-12-09 00:00:00'} AND ("F470LD"."F470DTAVEDEB" IS NULL OR "F470LD"."F470DTAVEDEB"<{ts '2010-12-08 00:00:00'}) AND ("F470LD"."F470DTAVEFIN" IS NULL OR "F470LD"."F470DTAVEFIN">={ts '2010-12-08 00:00:00'}) AND ("F470LD"."F470DTARR" IS NULL OR "F470LD"."F470DTARR">={ts '2010-12-09 00:00:00'}) AND ("F050TIERS"."F050KY">='' AND "F050TIERS"."F050KY"<='9999999') AND "F470LD"."K470T46TYP"<>'INT' ORDER BY "F050TIERS"."F050KY", "F470LD"."F470DTDEP"


      And here what I've done so far :

       

       

      QUALIFY*;

      F090PARC:
      SELECT F090KY, F090LIB, K090T07TYP
      FROM F090PARC;

      F091IMMAT:
      SELECT F091IMMA
      FROM F091IMMAT;

      F470LD:
      SELECT F470DTDEP, F470DUREE, F470DTARRP, F470DTARR, F470DTAVEDEB, F470DTAVEFIN, K470T46TYP
      FROM F470LD;

      F050TIERS:
      SELECT F050KY, F050NOM
      FROM F050TIERS;

      F050TIERS_COMM:
      SELECT F050PRENOM, F050NOM
      FROM F050TIERS;

      F050TIERS_COND:
      SELECT F050NOM, F050PRENOM
      FROM F050TIERS;

      Parc:
      LOAD
      F090PARC.F090KY,
      F090PARC.F090LIB,
      F091IMMAT.F091IMMA,
      F470LD.F470DTDEP,
      F470LD.F470DUREE,
      F470LD.F470DTARRP,
      F050TIERS.F050KY,
      F050TIERS.F050NOM,
      F050TIERS_COMM.F050PRENOM,
      F050TIERS_COMM.F050NOM,
      F090PARC.K090T07TYP,
      F470LD.F470DTARR,
      F470LD.F470DTAVEDEB,
      F470LD.F470DTAVEFIN,
      F470LD.K470T46TYP,
      F050TIERS_COND.F050NOM,
      F050TIERS_COND.F050PRENOM
      RESIDENT
      (
      (
      (
      (
      (
      F090PARC LEFT JOIN F570MVT ON F090PARC.F090KY=F570MVT.K570090UNI
      )
      LEFT JOIN F091IMMAT ON (F090PARC.K090091IMM=F091IMMAT.F091KY) AND (F090PARC.F090KY=F091IMMAT.K091090UNI)
      )
      LEFT JOIN F470LD ON F570MVT.F570KY=F470LD.K470570MVT
      )
      LEFT JOIN F050TIERS ON F470LD.K470050TIE=F050TIERS.F050KY)
      LEFT JOIN F050TIERS_COND ON F470LD.K470050CON=F050TIERS_COND.F050KY
      )
      LEFT JOIN F050TIERS_COMM ON F050TIERS.K050050COM=F050TIERS_COMM.F050KY
      WHERE
      F090PARC.F090KY <> '9999999999' AND
      F470LD.F470DTDEP IS NOT NULL AND
      F470LD.F470DTDEP < {ts '2010-12-09 00:00:00'} AND
      (
      F470LD.F470DTAVEDEB IS NULL OR
      F470LD.F470DTAVEDEB < {ts '2010-12-08 00:00:00'}
      ) AND
      (
      F470LD.F470DTAVEFIN IS NULL OR
      F470LD.F470DTAVEFIN >= {ts '2010-12-08 00:00:00'}
      ) AND
      (F470LD.F470DTARR IS NULL OR
      F470LD.F470DTARR >= {ts '2010-12-09 00:00:00'}
      ) AND
      (
      F050TIERS.F050KY >= '' AND
      F050TIERS.F050KY <= '9999999'
      ) AND
      F470LD.K470T46TYP <> 'INT'
      ORDER BY F050TIERS.F050KY, F470LD.F470DTDEP;


      Could you tell me what i've done wrong or right ?

        • Questions about SQL Server Query to Qlikview Query
          Rob Wunderlich

          Welcome to Qlikview.

          The JOIN keywords following the LOAD statement are incorrect. JOIN works differently in a QV load statement. Before we spend too much time analyzing your effort, have you considered just using the original SQL statement as is in the script? Or adding a preceeding LOAD if you want to manipulate the results with QV fuinctions? For example:

          MyTable:
          LOAD *
          ;
          your orginal sql statement
          ;

           

          -Rob