Bonjour,
Je dois créer un canonical calendar pour me permettre de comparer des données avec des périmètres de dates différentes.
Dans mon cas, je cherche à avoir l'ensemble des missions reçues en N et l'ensemble des réclamations reçues sur l'année N.
Une mission peut-etre recue en N avec une réclamation en N+1.
Pour cela, je crois comprendre que je dois créer un canonicalcalendar. Et la je commence à être perdue. Pouvez-vous m'aider ?
Les dates concernées par ce calendrier sont :
- Date_Dossier : Correspondant à la date de ma mission
- Date_Heure_Creation : Correspondant à la date de ma réclamation
J'ai chargé mes deux tables :
Set dataManagerTables = '','suivi','gaq_suivi';
//This block renames script tables from non generated section which conflict with the names of managed tables
For each name in $(dataManagerTables)
Let index = 0;
Let currentName = name;
Let tableNumber = TableNumber(name);
Let matches = 0;
Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
index = index + 1;
currentName = name & '-' & index;
tableNumber = TableNumber(currentName)
matches = Match('$(currentName)', $(dataManagerTables));
Loop
If index > 0 then
Rename Table [$(name)] to [$(currentName)];
EndIf;
Next;
LIB CONNECT TO [CapsOffice];
[suivi]:
LOAD
[Num_Dossier],
[Date_Dossier],
Year([Date_Dossier]) AS [Année_Dossier],
[Vehicule_Roulant],
[Id_Type_Incident],
[Id_Assureur] AS [suivi.Id_Assureur],
[Id_Garage] AS [suivi.Id_Garage],
[Mission_Premium],
[Mission_NMD],
[Mission_Hors_Reseau];
SQL SELECT `Num_Dossier`,
`Date_Dossier`,
`Vehicule_Roulant`,
`Id_Type_Incident`,
`Id_Assureur`,
`Id_Garage`,
`Mission_Premium`,
`Mission_NMD`,
`Mission_Hors_Reseau`
FROM `capsoffice`.`suivi`;
[gaq_suivi]:
LOAD
[Id_Reclamation],
[Num_Dossier],
[Id_Responsabilite],
[Id_Canal],
[Statut_Reclamation],
[Id_Garage] AS [gaq_suivi.Id_Garage],
[Id_Assureur] AS [gaq_suivi.Id_Assureur],
[Id_Type_Reclamation],
[Id_Identification],
[Date_Heure_Creation],
[Reclamation_Occurrence],
Year([Date_Heure_Creation]) AS [Année_creation_reclamation];
SQL SELECT `Id_Reclamation`,
`Num_Dossier`,
`Id_Responsabilite`,
`Id_Canal`,
`Statut_Reclamation`,
`Id_Garage`,
`Id_Assureur`,
`Id_Type_Reclamation`,
`Id_Identification`,
`Date_Heure_Creation`,
`Reclamation_Occurrence`
FROM `capsoffice`.`gaq_suivi`;
Un calendrier s'est créé automatiquement :
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [Date_Dossier], [Date_Heure_Creation] USING [autoCalendar] ;
Comment procéder à partir de ces éléments pour créer mon canonicalcalendar ?
Encore merci aux personnes qui pourront m'aider ?
Stéphanie
