topic Avoid loops in QlikView

Avoid loops

souadouert — Wed, 01 Mar 2017 09:49:46 GMT

i tried to found a solution to avoid loops in my modele

SCRIPT

tregion:

SQL SELECT CODEREGION,

LIBELLEREGION

FROM REGION;

tzone:

SQL SELECT CODEREGION,

CODEZONE,

LIBELLEZONE

FROM TZONE;

tagence:

SQL SELECT ADRESS,

CHEFAGENCE,

CODEAGENCE ,

CODEZONE

FROM AGENCE;

tobjectifagence:

SQL SELECT CODEAGENCE as CODEAGENCE ,

OBJECTIF,

CODEOBJECTIF ,

CODEPRODUIT,

DATEOBJECTIF

FROM OBJECTIFAGENCE;

trealisationagence:

SQL SELECT CODEAGENCE as CODEAGENCE,

REALISATION,

CODEPRODUIT ,

CODEVENTE,

DATEVENTE

FROM VENTEPRODUITAGENCE;

calendrier:

SQL SELECT

Switch( day(`DATEVENTE`)<16,"Q1",

day(`DATEVENTE`)>15,"Q2") as 'quinzaine',

DAY(`DATEVENTE`) as jour,

MONTH(`DATEVENTE`) as mois,

Switch(

MONTH(`DATEVENTE`)=1,"Janvier",

MONTH(`DATEVENTE`)=2,"Février",

MONTH(`DATEVENTE`)=3,"Mars",

MONTH(`DATEVENTE`)=4,"Avril",

MONTH(`DATEVENTE`)=5,"Mai",

MONTH(`DATEVENTE`)=6,"Juin",

MONTH(`DATEVENTE`)=7,"Juillet",

MONTH(`DATEVENTE`)=8,"Août",

MONTH(`DATEVENTE`)=9,"Septembre",

MONTH(`DATEVENTE`)=10,"Octobre",

MONTH(`DATEVENTE`)=11,"Novembre",

MONTH(`DATEVENTE`)=12,"Decembre") as NOMMOIS,

Switch(

MONTH(`DATEVENTE`)=1,"Janv.",

MONTH(`DATEVENTE`)=2,"Fév.",

MONTH(`DATEVENTE`)=3,"Mars",

MONTH(`DATEVENTE`)=4,"Avr.",

MONTH(`DATEVENTE`)=5,"Mai",

MONTH(`DATEVENTE`)=6,"Juin",

MONTH(`DATEVENTE`)=7,"Juill.",

MONTH(`DATEVENTE`)=8,"Août",

MONTH(`DATEVENTE`)=9,"Sept",

MONTH(`DATEVENTE`)=10,"Oct.",

MONTH(`DATEVENTE`)=11,"Nov.",

MONTH(`DATEVENTE`)=12,"Dec.") as CODEMOIS,

YEAR(`DATEVENTE`) as année,

DATEVENTE

FROM `VENTEPRODUITAGENCE`

order by `DATEVENTE`;

tproduit:

SQL SELECT CODEPRODUIT ,

CODEPRODUIT,

LIBELLEPRODUIT

FROM Produit;

AND THIS IS THE MODEL/

Re: Avoid loops

adamdavi3s — Wed, 01 Mar 2017 09:51:56 GMT

Technically there is nothing at all wrong with a synthetic key However you can work around it with a quick link table or you can concatenate the fields to make a key to join on (I prefer links myself)

Re: Avoid loops

souadouert — Wed, 01 Mar 2017 10:00:46 GMT

good morning adam , i know that, But Preferably in MDD , we have to avoid this type of loop.

Re: Avoid loops

adamdavi3s — Wed, 01 Mar 2017 10:12:46 GMT

Hi Souad,

I suspect I am missing the point?

Is it the synthetic key you are wanting to remove from the data model?

Re: Avoid loops

souadouert — Wed, 01 Mar 2017 10:18:29 GMT

yess

Re: Avoid loops

adamdavi3s — Wed, 01 Mar 2017 11:09:15 GMT

Ok please can you try this out, sorry there might be some small mistakes as obviously I can't try it out (don;t have access to your DB to load)

tregion:

SQL SELECT CODEREGION,

LIBELLEREGION

FROM REGION;

tzone:

SQL SELECT CODEREGION,

CODEZONE,

LIBELLEZONE

FROM TZONE;

tagence:

SQL SELECT ADRESS,

CHEFAGENCE,

CODEAGENCE ,

CODEZONE

FROM AGENCE;

tobjectifagence:

LOAD *, rowno() as tobjectifagence_id;

SQL SELECT CODEAGENCE as tobjectifagence_CODEAGENCE ,

OBJECTIF,

CODEOBJECTIF ,

CODEPRODUIT as tobjectifagence_CODEPRODUIT,

DATEOBJECTIF

FROM OBJECTIFAGENCE;

trealisationagence:

LOAD *, rowno() as trealisationagence_id;

SQL SELECT CODEAGENCE as trealisationagence_CODEAGENCE,

REALISATION,

CODEPRODUIT as trealisationagence_CODEPRODUIT ,

CODEVENTE,

DATEVENTE

FROM VENTEPRODUITAGENCE;

calendrier:

SQL SELECT

Switch( day(`DATEVENTE`)<16,"Q1",

day(`DATEVENTE`)>15,"Q2") as 'quinzaine',

DAY(`DATEVENTE`) as jour,

MONTH(`DATEVENTE`) as mois,

Switch(

MONTH(`DATEVENTE`)=1,"Janvier",

MONTH(`DATEVENTE`)=2,"Février",

MONTH(`DATEVENTE`)=3,"Mars",

MONTH(`DATEVENTE`)=4,"Avril",

MONTH(`DATEVENTE`)=5,"Mai",

MONTH(`DATEVENTE`)=6,"Juin",

MONTH(`DATEVENTE`)=7,"Juillet",

MONTH(`DATEVENTE`)=8,"Août",

MONTH(`DATEVENTE`)=9,"Septembre",

MONTH(`DATEVENTE`)=10,"Octobre",

MONTH(`DATEVENTE`)=11,"Novembre",

MONTH(`DATEVENTE`)=12,"Decembre") as NOMMOIS,

Switch(

MONTH(`DATEVENTE`)=1,"Janv.",

MONTH(`DATEVENTE`)=2,"Fév.",

MONTH(`DATEVENTE`)=3,"Mars",

MONTH(`DATEVENTE`)=4,"Avr.",

MONTH(`DATEVENTE`)=5,"Mai",

MONTH(`DATEVENTE`)=6,"Juin",

MONTH(`DATEVENTE`)=7,"Juill.",

MONTH(`DATEVENTE`)=8,"Août",

MONTH(`DATEVENTE`)=9,"Sept",

MONTH(`DATEVENTE`)=10,"Oct.",

MONTH(`DATEVENTE`)=11,"Nov.",

MONTH(`DATEVENTE`)=12,"Dec.") as CODEMOIS,

YEAR(`DATEVENTE`) as année,

DATEVENTE

FROM `VENTEPRODUITAGENCE`

order by `DATEVENTE`;

tproduit:

SQL SELECT CODEPRODUIT ,

CODEPRODUIT,

LIBELLEPRODUIT

FROM Produit;

LinkTable:

LOAD tobjectifagence_id,

tobjectifagence_CODEAGENCE as CODEAGENCE,

tobjectifagence_CODEPRODUIT as CODEPRODUIT

resident tobjectifagence;

Concatenate

LOAD trealisationagence_id,

trealisationagence_CODEAGENCE as CODEAGENCE,

trealisationagence_CODEPRODUIT as CODEPRODUIT

resident trealisationagence;

drop fields tobjectifagence_CODEAGENCE,tobjectifagence_CODEPRODUIT,trealisationagence_CODEAGENCE,trealisationagence_CODEPRODUIT;

Re: Avoid loops

souadouert — Wed, 01 Mar 2017 11:55:03 GMT

thank you adam You helped me a lot

Re: Avoid loops

adamdavi3s — Wed, 01 Mar 2017 11:59:45 GMT

No problem at all, glad to be of help