Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Bonjour,
Je rencontre 2 problèmes pour écrire mon script.
Dans un premier temps, je cherche à calculer la DATE_FIN maximum précédente à ma DATE_EFFET.
Dans un deuxième temps, je souhaite corriger ces valeurs si elles sont déjà présentes à l'intérieur d'un intervalle (uniquement pour les CDD).
Exemple :
DATE_EFFET | DATE_FIN | TYPE_CONTRAT | VALEUR MAX A TROUVER | CORRECTION DES VALEURS | VALEUR FINALE |
01/01/2016 | 03/02/2016 | CDD | rien car pas de ligne avant | ||
07/08/2015 | 13/12/2015 | CDD | rien car autre matricule avant | ||
09/01/2016 | 31/01/2016 | CDD | 13/12/2015 | IDEM Colonne D | 13/12/2015 |
10/01/2016 | 31/03/2017 | CDI | 13/12/2015 | IDEM Colonne D | 13/12/2015 |
22/01/2016 | 22/01/2016 | CDD | 13/12/2015 | étant donné que DATE_EFFET de ce CDD se trouve dans l'intervalle d'un autre CDD (09/01/2016 au 31/01/2016), je veux 22/01/2016 (DATE_EFFET) | 22/01/2016 |
30/01/2016 | 30/01/2016 | CDD | 22/01/2016 | étant donné que DATE_EFFET de ce CDD se trouve dans l'intervalle d'un autre CDD (09/01/2016 au 31/01/2016), je veux 30/01/2016 (DATE_EFFET) | 30/01/2016 |
01/02/2016 | 29/02/2016 | CDD | 31/01/2016 | IDEM Colonne D | 31/01/2016 |
19/02/2016 | 27/02/2016 | CDD | 31/01/2016 | étant donné que DATE_EFFET de ce CDD se trouve dans l'intervalle d'un autre CDD (01/02/2016 au 29/02/2016), je veux 19/02/2016 (DATE_EFFET) | 19/02/2016 |
01/03/2016 | 31/03/2016 | CDD | 29/02/2016 | IDEM Colonne D | 29/02/2016 |
21/03/2016 | 21/03/2016 | CDD | 29/02/2016 | étant donné que DATE_EFFET de ce CDD se trouve dans l'intervalle d'un autre CDD (01/03/2016 au 31/03/2016), je veux 21/03/2016 (DATE_EFFET) | 21/03/2016 |
01/04/2016 | 30/04/2016 | CDD | 31/03/2016 | IDEM Colonne D | 31/03/2016 |
01/05/2016 | 31/05/2016 | CDD | 30/04/2016 | IDEM Colonne D | 30/04/2016 |
01/06/2016 | 30/06/2016 | CDD | 31/05/2016 | IDEM Colonne D | 31/05/2016 |
01/07/2016 | 01/08/2016 | CDD | 30/06/2016 | IDEM Colonne D | 30/06/2016 |
02/08/2016 | 31/08/2016 | CDD | 01/08/2016 | IDEM Colonne D | 01/08/2016 |
Si je ne suis pas assez clair, n'hésitez pas à me demander des précisions.
Je vous mets en pièce jointe le fichier excel qui pourrait vous servir de jeu d'essai (Colonne A à D)
Merci d'avance pour votre aide.
Kevin
Pour la première partie je pense qu'il faut que je passe quelque chose comme :
Date(RangeMax(peek(DATE_FIN),peek(DATE_FIN,-3)),'DD/MM/YYYY')
Reste à voir comment je peux modifier -3 par une variable indiquant le nombre de lignes précédents pour le matricule.
J'ai essayé ceci mais ça ne fonctionne pas :
Date(RangeMax(peek(DATE_FIN),peek(DATE_FIN,-(RowNo()-FirstSortedValue(RowNo(), MATRICULE)))),'DD/MM/YYYY')
Si quelqu'un a une idée...
Merci Kevin
Si c'est plus simple, on peut regrouper les deux étapes :
L'objectif est de trouver la DATE_FIN maximum sur les lignes au dessus (sans prendre en compte les CDI).
Merci
Kevin
Il n'est pas possible de réaliser ceci avec une expression dans le script de chargement car aucune dès fonction inter-enregistrement utilisable dans le script de chargement ne permet de retourner une plage de données (DateFin de la ligne précédente à la 1ère ligne) afin d'en trouver la valeur Max() inférieure à la DateEffet.
Je pense qu'il faut créer une table temporaire qui met en relation chaque DateEffet avec les DateFin précédentes. Il sera alors possible avec cette table de trouver les DateFin max pour chaque DateEffet.
je n'ai pas de temps aujourd'hui, mais je pourrais regarder ceci demain
J'ai un script pour la 1ère partie, par contre à la ligne 4 j'ai 31/01/2016 comme VALEUR MAX À TROUVER et pas 13/12/2015.
As-tu fait une erreur, ou y a-t-il une règle particulière qui explique ça ?
Merci Sebastien.
Pour la ligne 4, ce n'est pas très grave. Les CDI sont gérés différemment. Mon but est ensuite de calculer les mouvements d'entrée.
J'ai continué à travailler dessus cette semaine. Je commence à me rapprocher de ce que je veux en créant une table CDD et une table CDI. Je mettrais mon script Lundi car à mon avis il y a mieux à faire...
Kevin
Voici mon script pour la 1ère partie, la seconde partie peut être réalisée sur le même principe.
Merci Sebastien. Je vais regarder cela dès que j'aurai un moment./
Pour ma part, voici la solution que j'ai trouvé pour le moment (PERSON = MATRICULE / DAT_ENT_ETABL = DATE_DEBUT / DAT_DEP_ETABL = DATE_FIN) :
// Création d'une table spécifique pour les CDD
CDD_TEMP:
NoConcatenate
Load DISTINCT *, RowNo() as RowNo
RESIDENT Link_TEMP
WHERE len(DAT_ENT_ETABL)>0 and TYPE_CONTRAT='CDD'
order by PERSON_ID asc, DAT_ENT_ETABL asc,Date_ref asc;
CDD_TEMP_2 :
NoConcatenate
Load DISTINCT PERSON_ID,
FirstSortedValue(DISTINCT RowNo,num(Date#(DAT_ENT_ETABL,'DD/MM/YYYY'))) as NUM,
FirstSortedValue(DISTINCT RowNo,-num(Date#(DAT_ENT_ETABL,'DD/MM/YYYY'))) as NUM_SUIVANT
RESIDENT CDD_TEMP
WHERE len(DAT_ENT_ETABL)>0 and TYPE_CONTRAT='CDD'
group by PERSON_ID;
TABLE_CDD:
NoConcatenate
Load *
RESIDENT CDD_TEMP
order by PERSON_ID asc, DAT_ENT_ETABL asc,Date_ref asc;
left join
Load *
RESIDENT CDD_TEMP_2;
DROP TABLE CDD_TEMP;
DROP TABLE CDD_TEMP_2;
// CALCUL DE LA DATE D'ENTREE SUIVANTE
DATE_ENTREE_SUIVANTE:
NoConcatenate
LOAD
PERSON_ID,
Date_ref,
DAT_ENT_ETABL,
DAT_DEP_ETABL,
TYPE_CONTRAT,
Date(RangeMin(peek(DAT_ENT_ETABL,-(RowNo-NUM_SUIVANT)),peek(DAT_ENT_ETABL)),'DD/MM/YYYY') as DAT_ENT_ETABL_SVT
RESIDENT TABLE_CDD
order by PERSON_ID desc, Date_ref desc,DAT_ENT_ETABL desc ;
// Calcul des MVT_ENTREE pour les CDD
MVT_ENTREE_SORTIE_CDD :
NoConcatenate
Load DISTINCT *,
IF(Previous(DAT_DEP_ETABL)>DAT_ENT_ETABL,peek('MOTIF_DEPART_PRECEDENT'),Previous(MOTIF_DEPART)) as MOTIF_DEPART_PRECEDENT,
IF(NUM<>PREVIOUS(NUM),DAT_ENT_ETABL,Date(RangeMax(peek(DAT_DEP_ETABL,-(RowNo-NUM)),peek(DAT_DEP_ETABL)),'DD/MM/YYYY')) as DAT_DEP_ETABL_PRECEDENT,
IF(DAT_ENT_ETABL-IF(NUM<>PREVIOUS(NUM),DAT_ENT_ETABL,Date(RangeMax(peek(DAT_DEP_ETABL,-(RowNo-NUM)),peek(DAT_DEP_ETABL)),'DD/MM/YYYY'))>1 AND DAT_ENT_ETABL=Date_ref,1,0) as MVT_ENTREE_CDD
RESIDENT TABLE_CDD
order by PERSON_ID asc,Date_ref asc,DAT_DEP_ETABL asc,DAT_ENT_ETABL asc;
left join
LOAD DISTINCT *,
IF(PERSON_ID<>Previous(PERSON_ID) AND DAT_DEP_ETABL=Date_ref,1,IF(DAT_ENT_ETABL_SVT-DAT_DEP_ETABL>1 AND DAT_DEP_ETABL=Date_ref,1,0)) as MVT_SORTIE_CDD
RESIDENT DATE_ENTREE_SUIVANTE;
DROP TABLE TABLE_CDD;
DROP TABLE DATE_ENTREE_SUIVANTE;
MVT_ENTREE_SORTIE_CDI:
NoConcatenate
Load DISTINCT
PERSON_ID,
DAT_ENT_ETABL,
DAT_DEP_ETABL,
Date_ref,
MOTIF_DEPART,
IF(Previous(DAT_DEP_ETABL)>DAT_ENT_ETABL,peek('MOTIF_DEPART_PRECEDENT'),Previous(MOTIF_DEPART)) as MOTIF_DEPART_PRECEDENT,
If(DAT_ENT_ETABL=Date_ref,1,0) as MVT_ENTREE_CDI,
If(DAT_DEP_ETABL=Date_ref,1,0) as MVT_SORTIE_CDI
RESIDENT Link_TEMP
WHERE len(DAT_ENT_ETABL)>0 and TYPE_CONTRAT='CDI';
// TABLE SOUS_TRAITANCE A PART
SOUS_TRAITANCE:
NoConcatenate
LOAD Distinct
PERSON_ID,
Date_ref,
date(Date_ref,'DD/MM/YYYY') as DATE_DEBUT,
date(Date_ref,'DD/MM/YYYY') as DATE_FIN,
NOM_PRENOM,
LIEU,
ETABLISSEMENT,
TYPE_SOUS_TRAITANCE,
SOUS_TRAITANT,
DETAIL_UTILISEE
RESIDENT Link_TEMP
where IsText(PERSON_ID) <> 0;
DROP TABLE Link_TEMP;
// Concaténation des tables en une seule
Dim_DonneesDatees_temp:
NoConcatenate
LOAD
*,
date(Date_ref,'DD/MM/YYYY') as DATE_DEBUT,
date(Date_ref,'DD/MM/YYYY') as DATE_FIN,
RowNo() as NUM_LIGNE
RESIDENT MVT_ENTREE_SORTIE_CDD
order by PERSON_ID asc, DAT_ENT_ETABL asc, Date_ref asc;
JOIN
LOAD *
RESIDENT MVT_ENTREE_SORTIE_CDI;
drop table MVT_ENTREE_SORTIE_CDD;
drop table MVT_ENTREE_SORTIE_CDI;
Dim_DonneesDatees_temp2:
LOAD *,
IF(Previous(PERSON_ID)<>PERSON_ID,If(DAT_ENT_ETABL=Date_ref,1,0)) as MVT_PREMIERE_ENTREE
RESIDENT Dim_DonneesDatees_temp
order by PERSON_ID asc,Date_ref asc,DAT_ENT_ETABL asc, DAT_DEP_ETABL asc;
DROP TABLE Dim_DonneesDatees_temp;
Dim_DonneesDatees:
LOAD *,
(alt(MVT_ENTREE_CDD,0) + alt(MVT_ENTREE_CDI,0) + alt(MVT_PREMIERE_ENTREE,0)) as MVT_ENTREE,
(alt(MVT_SORTIE_CDD,0) + alt(MVT_SORTIE_CDI,0)) as MVT_SORTIE
RESIDENT Dim_DonneesDatees_temp2;
left JOIN
LOAD Distinct
PERSON_ID,DATE_NAISSANCE
RESIDENT DATE_NAISSANCE where not IsNull(DATE_NAISSANCE);
JOIN
Load *
RESIDENT SOUS_TRAITANCE;
DROP TABLE Dim_DonneesDatees_temp2;
drop table DATE_NAISSANCE;
drop table SOUS_TRAITANCE;
STORE Dim_DonneesDatees into $(FicQVD_DimDonneesDatees_Layer1) (qvd);
exit script;
Merci de ton aide Sebastien.
J'ai pris le temps de regarder le fichier. Je crois qu'il y a une petite erreur sur le 22/01/2016 mais sinon bravo !
J'ai finalement optimisé mon script précédemment posté dans cette discussion. J'obtiens ce que je veux.
Kevin
Bonjour Sebastien,
Je reviens tardivement sur ce post. Ta méthode me convenait presque mais c'est dommage car j'ai des millions de lignes donc le FOR EACH est beaucoup trop long dans mon cas (en 5 minutes, seulement 200 lignes chargées dans la boucle sur des millions....)
Je suis confronté à une réelle problématique avec les données de mon client car je dois calculer la différence de jour entre la fin du contrat précédent et le contrat en cours.
Le problème comme tu as pu le voir c'est que je ne peux pas faire un simple previous() car je me retrouve avec des dates fin de contrat précédent > au contrat en cours.
Penses-tu qu'il y ait une autre méthode pour retrouver la date la plus élevée sur les lignes précédentes (tout en prenant soin qu'elle soit inférieure à la date de début) ? De manière à ne pas prendre par exemple, 31.03.2017 pour le contrat qui commence le 22.01.2016.
Kevin