Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

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 ?

Tags (2)
1 Reply
MVP & Luminary
MVP & Luminary

Questions about SQL Server Query to Qlikview Query

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