Re: Valeur max précédente - Qlik Community

kevinchevrier · ‎2017-07-18

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

kevinchevrier · ‎2017-07-18

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

kevinchevrier · ‎2017-07-19

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

sfatoux72 · ‎2017-07-20

‌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

sfatoux72 · ‎2017-07-21

‌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 ?

kevinchevrier · ‎2017-07-22

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

sfatoux72 · ‎2017-07-24

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

kevinchevrier · ‎2017-07-24

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;

kevinchevrier · ‎2017-07-27

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

kevinchevrier · ‎2017-08-09

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