Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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/
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;
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)
good morning adam , i know that, But Preferably in MDD , we have to avoid this type of loop.
Hi Souad,
I suspect I am missing the point?
Is it the synthetic key you are wanting to remove from the data model?
yess
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;
thank you adam You helped me a lot
No problem at all, glad to be of help