Questions about SQL Server Query to Qlikview Query

Wed, 15 Dec 2010 12:29:35 GMT

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

rwunderlich — Thu, 16 Dec 2010 00:27:28 GMT

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

topic Questions about SQL Server Query to Qlikview Query in QlikView

Questions about SQL Server Query to Qlikview Query

Questions about SQL Server Query to Qlikview Query