9 Replies Latest reply: Aug 9, 2017 9:43 AM by Kevin Chevrier RSS

    Valeur max précédente

    Kevin Chevrier

      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_EFFETDATE_FINTYPE_CONTRATVALEUR MAX A TROUVERCORRECTION DES VALEURSVALEUR FINALE
      01/01/201603/02/2016CDDrien car pas de ligne avant
      07/08/201513/12/2015CDDrien car autre matricule avant
      09/01/201631/01/2016CDD13/12/2015IDEM Colonne D13/12/2015
      10/01/201631/03/2017CDI13/12/2015IDEM Colonne D13/12/2015
      22/01/201622/01/2016CDD13/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/201630/01/2016CDD22/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/201629/02/2016CDD31/01/2016IDEM Colonne D31/01/2016
      19/02/201627/02/2016CDD31/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/201631/03/2016CDD29/02/2016IDEM Colonne D29/02/2016
      21/03/201621/03/2016CDD29/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/201630/04/2016CDD31/03/2016IDEM Colonne D31/03/2016
      01/05/201631/05/2016CDD30/04/2016IDEM Colonne D30/04/2016
      01/06/201630/06/2016CDD31/05/2016IDEM Colonne D31/05/2016
      01/07/201601/08/2016CDD30/06/2016IDEM Colonne D30/06/2016
      02/08/201631/08/2016CDD01/08/2016IDEM Colonne D01/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

        • Re: Valeur max précédente
          Kevin Chevrier

          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

            • Re: Valeur max précédente
              Kevin Chevrier

              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

                • Re: Valeur max précédente
                  Sébastien Fatoux

                  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

                    • Re: Valeur max précédente
                      Sébastien Fatoux

                      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 ?

                        • Re: Valeur max précédente
                          Kevin Chevrier

                          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

                            • Re: Valeur max précédente
                              Sébastien Fatoux

                              Voici mon script pour la 1ère partie, la seconde partie peut être réalisée sur le même principe.

                                • Re: Valeur max précédente
                                  Kevin Chevrier

                                  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;

                                  • Re: Valeur max précédente
                                    Kevin Chevrier

                                    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

                                    • Re: Valeur max précédente
                                      Kevin Chevrier

                                      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