Canonical Date

[stephaniee]

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

[Anonymous]

Bonjour Stéphanie,

Je n'avais jamais entendus le mot "Canonical Calendar"

Tu peux aller voir se post qui explique comment faire tes différents calendriers

Canonical Date

Je peux te mettre ci-dessous le calendrier que j'utilise (tu pourras charger le script tel quel)

// Calendrier sur la date [Date_Dossier] de la table [suivi] => Suivi_Date_Dossier

[MinMaxDates]:

Load

    Min(Floor([Date_Dossier]))  AS [MinDate],

    Max(Floor([Date_Dossier]))  AS [MaxDate]

Resident [suivi]

;

Let vMinDate = Peek('MinDate', 0, 'MinMaxDates')*1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDates')*1;

Drop Table [MinMaxDates];

[tmp_Calendar]:

Load

  Date($(vMinDate) + RowNo()-1) AS [Date]

    AutoGenerate ($(vMaxDate) - $(vMinDate)+1)

    ;

// Calendrier du Suivis des Dates Dossier => Nom de table : [Calendar_SDD]   

[Calendar_SDD]:

Load

  [Date]           As [Date_SDD],

  Date([Date],'DD/MM/YYYY')  AS [Date__SDD],

  MakeWeekDate(Year([Date])-1, Week([Date]), Num(WeekDay([Date])))  AS [Day_Date_Y-1__SDD], // Day Name Equiv N-1

  AddYears([Date],-1)        AS [Date_Y-1__SDD], // Date Equiv N-1

  Date([Date],'DD')          AS [Num_Day_SDD],

  Date([Date],'YYYYMMDD')    AS [FullDate_SDD],

  Week([Date])               AS [Week_SDD],

  WeekName([Date])           AS [Week_Name_SDD],

  Year(Date)-1 & '/' & Num(Week(Date),'00')     AS [Week_Name_Y-1_SDD],  // Num Semaine Equiv N-1

  Num(Week([Date]))          AS [Week_Num_SDD],

  Month([Date])              AS [Month_SDD],

  MonthName([Date])          AS [Month_Name_SDD],

  Num(Month([Date]))         AS [Month_Num_SDD],

  Year([Date])               AS [Year_SDD],

  Year([Date]) & '.' & Week([Date])   AS [Year_Week_SDD],

  Week([Date]) & '.' & Year([Date])   AS [Week_Year_SDD],

  Num(Dual(Year([Date])&'-'&Week([Date]), weekstart([Date])))     AS [Year_Week_Num_SDD],

  Year([Date]) & '.' & Month([Date])  AS [Year_Month_SDD],

  Month([Date]) & '.' & Year([Date])  AS [Month_Year_SDD],

  Num(Dual(Year([Date])&'-'&Month([Date]), monthstart([Date])))   AS [Year_Month_Num_SDD],

  Dual('Q'&Num(Ceil(Num(Month([Date]))/3)),Num(Ceil(Num(Month([Date]))/3),00))  AS [Qarter_SDD],

  Dual(Year([Date])&'-Q'&Num(Ceil(Num(Month([Date]))/3)),QuarterStart([Date]))         AS [Year_Quarter_SDD]

Resident [tmp_Calendar]

;

Drop Tables [tmp_Calendar];

/*************************************************************************************************************/

/*************************************************************************************************************/

// Calendrier sur la date [Date_Heure_Creation] de la table [gaq_suivi] => Suivi_Date_Réclamation

[MinMaxDates]:

Load

    Min(Floor([Date_Heure_Creation]))  AS [MinDate],

    Max(Floor([Date_Heure_Creation]))  AS [MaxDate]

Resident [gaq_suivi]

;

Let vMinDate = Peek('MinDate', 0, 'MinMaxDates')*1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDates')*1;

Drop Table [MinMaxDates];

[tmp_Calendar]:

Load

  Date($(vMinDate) + RowNo()-1) AS [Date]

    AutoGenerate ($(vMaxDate) - $(vMinDate)+1)

    ;

// Calendrier du Suivis des Dates Réclamation => Nom de table : [Calendar_SDR]   

[Calendar_SDR]:

Load

  [Date]           As [Date_SDR],

  Date([Date],'DD/MM/YYYY')  AS [Date_SDR],

  MakeWeekDate(Year([Date])-1, Week([Date]), Num(WeekDay([Date])))  AS [Day_Date_Y-1_SDR], // Day Name Equiv N-1

  AddYears([Date],-1)        AS [Date_Y-1_SDR], // Date Equiv N-1

  Date([Date],'DD')          AS [Num_Day_SDR],

  Date([Date],'YYYYMMDD')    AS [FullDate_SDR],

  Week([Date])               AS [Week_SDR],

  WeekName([Date])           AS [Week_Name_SDR],

  Year(Date)-1 & '/' & Num(Week(Date),'00')     AS [Week_Name_Y-1_SDR],  // Num Semaine Equiv N-1

  Num(Week([Date]))          AS [Week_Num_SDR],

  Month([Date])              AS [Month_SDR],

  MonthName([Date])          AS [Month_Name_SDR],

  Num(Month([Date]))         AS [Month_Num_SDR],

  Year([Date])               AS [Year_SDR],

  Year([Date]) & '.' & Week([Date])   AS [Year_Week_SDR],

  Week([Date]) & '.' & Year([Date])   AS [Week_Year_SDR],

  Num(Dual(Year([Date])&'-'&Week([Date]), weekstart([Date])))     AS [Year_Week_Num_SDR],

  Year([Date]) & '.' & Month([Date])  AS [Year_Month_SDR],

  Month([Date]) & '.' & Year([Date])  AS [Month_Year_SDR],

  Num(Dual(Year([Date])&'-'&Month([Date]), monthstart([Date])))   AS [Year_Month_Num_SDR],

  Dual('Q'&Num(Ceil(Num(Month([Date]))/3)),Num(Ceil(Num(Month([Date]))/3),00))  AS [Qarter_SDR],

  Dual(Year([Date])&'-Q'&Num(Ceil(Num(Month([Date]))/3)),QuarterStart([Date]))         AS [Year_Quarter_SDR]

Resident [tmp_Calendar]

;

Drop Tables [tmp_Calendar];

/*************************************************************************************************************/

/*************************************************************************************************************/

Concernant ta première demande essaye ça

// Nombre de dossier sur l'année Max (nombre de mission)

=Count({$< [Year_SDD] = {"$(=(Max([Year_SDD])))"} >} DISTINCT [Num_Dossier])

// Nombre de Réclamation sur l'année Max

=Count({$< [Year_SDR] = {"$(=(Max([Year_SDR])))"} >} DISTINCT [Num_Dossier])

[sfatoux72 · Accepted solution]

‌Salut,

Lors du chargement de la table "gaq_suivi", renome le champ "Num_Dossier" en "Tmp_Num_Dossier" (Num_Dossier as Tmp_Num_Dossier )

Puis ajoute le code ci-dessous après le chargement de la table "gaq_suivi" :

DateBridge:

Load

    Num_Dossier,

    Null() as Id_Reclamation,

    Date_Dossier as Canonical_Date

    'Date_Dossier' as DateType

Resident suivi;

Concatenate (DateBridge)

Load

    Tmp_Num_Dossier as Num_Dossier,

    Id_Reclamation,

    Date_Heure_Creation as Canonical_Date

    'Date_Reclamation' as DateType

Resident gaq_suivi;

Drop Field Tmp_Num_Dossier;

tu pourra alors construire ton calendrier sur le champ "Canonical_Date"

[stephaniee]

Salut,

Et merci beaucoup !

Action réalisée et mon chargement fonctionne. Mais je coince sur la création du calendrier.

En découle plusieurs questions :

- Est-ce que je dois créer un calendrier un parrallèle pour chaque date ? (En reprenant les éléments de Boris)

- Ou, est ce que mon calendrier créé automatiquement suffit ?

- Ou, dois je le supprimer ?

Mon calendrier créé à partir des éléments de Boris (que je remercie également).

// Calendrier sur la date [Canonical_Date] de la table [????????]

[MinMaxDates]:

Load

    Min(Floor([Canonical_Date]))  AS [MinDate],

    Max(Floor([Canonical_Date]))  AS [MaxDate]

Resident [?????????]

;

Let vMinDate = Peek('MinDate', 0, 'MinMaxDates')*1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDates')*1;

Drop Table [MinMaxDates];

[canonical_Calendar]:

Load

  Date($(vMinDate) + RowNo()-1) AS [Date]

    AutoGenerate ($(vMaxDate) - $(vMinDate)+1)

    ;

// Calendrier Globale  => Nom de table : [Calendar]  

[Calendar]:

Load

  [Date]           As [Date],

  Date([Date],'DD/MM/YYYY')  AS [Date],

  MakeWeekDate(Year([Date])-1, Week([Date]), Num(WeekDay([Date])))  AS [Day_Date_Y-1], // Day Name Equiv N-1

  AddYears([Date],-1)        AS [Date_Y-1], // Date Equiv N-1

  Date([Date],'DD')          AS [Num_Day],

  Date([Date],'YYYYMMDD')    AS [FullDate],

  Week([Date])               AS [Week],

  WeekName([Date])           AS [Week_Name],

  Year(Date)-1 & '/' & Num(Week(Date),'00')     AS [Week_Name_Y-1],  // Num Semaine Equiv N-1

  Num(Week([Date]))          AS [Week_Num],

  Month([Date])              AS [Month],

  MonthName([Date])          AS [Month_Name],

  Num(Month([Date]))         AS [Month_Num],

  Year([Date])               AS [Year],

  Year([Date]) & '.' & Week([Date])   AS [Year_Week],

  Week([Date]) & '.' & Year([Date])   AS [Week_Year],

  Num(Dual(Year([Date])&'-'&Week([Date]), weekstart([Date])))     AS [Year_Week_Num],

  Year([Date]) & '.' & Month([Date])  AS [Year_Month],

  Month([Date]) & '.' & Year([Date])  AS [Month_Year],

  Num(Dual(Year([Date])&'-'&Month([Date]), monthstart([Date])))   AS [Year_Month_Num],

  Dual('Q'&Num(Ceil(Num(Month([Date]))/3)),Num(Ceil(Num(Month([Date]))/3),00))  AS [Qarter],

  Dual(Year([Date])&'-Q'&Num(Ceil(Num(Month([Date]))/3)),QuarterStart([Date]))         AS [Year_Quarter]

Resident [???????????????]

;

Est-ce que mon calendrier est correct ? Pouvez-vous m'aider sur mes infomations manquantes ?

Encore merci pour votre aide à tous,

Stéphanie

[Anonymous]

Bonjour Stéphanie,

Suite au message de Sébastien je comprend mieux ton besoin.

- Est-ce que je dois créer un calendrier en parallèle pour chaque date ? (En reprenant les éléments de Boris)

     => Tout dépend de tes différents besoin dans ton reporting, ne maitrisant pas parfaitement le calendrier canonique je laisserais Sébastien te répondre plus précisément sur ce point.

- Ou, est ce que mon calendrier créé automatiquement suffit ?

     => J'ai vraiment une préférence pour le calendrier que j'ai posté dans mon message précédent par rapport à l'AutoCalendar mais l'AutoCalendar comme le Calendar posté plus haut feront le job correctement l'un comme l'autre.

- Ou, dois je le supprimer ?

     => Comme ci-dessus, à toi de voir le calendrier que tu préfère utilisé, si Sébastien repasse par ici je suis sur qu'il aura une bonne practice à proposer

Concernant ta dernière question :

- Est-ce que mon calendrier est correct ? Pouvez-vous m'aider sur mes informations manquantes ?

// Calendrier sur la date [Canonical_Date] de la table [DateBridge]

// Ton champ [Canonical_Date] se trouve dans la table [DateBridge], nous allons donc nous servir de cette table pour charger le champ [Canonical_Date] dans le calendrier ci-dessous

[MinMaxDates]:

Load

    Min(Floor([Canonical_Date]))  AS [MinDate],

    Max(Floor([Canonical_Date]))  AS [MaxDate]

Resident [DateBridge]  // ici on utilise la fonction "Resident" pour charger des données d'une table déjà chargé dans Qlik (ici la table est [DateBridge])

;

Let vMinDate = Peek('MinDate', 0, 'MinMaxDates')*1;

Let vMaxDate = Peek('MaxDate', 0, 'MinMaxDates')*1;

Drop Table [MinMaxDates];

// Pour la table ci-dessous et pour l'ensemble de tes calendriers tu peux laisser le nom de table [tmp_Calendar]

[tmp_Calendar]:

Load

  Date($(vMinDate) + RowNo()-1) AS [Date]

    AutoGenerate ($(vMaxDate) - $(vMinDate)+1)

    ;

// Calendrier Globale  => Nom de table : [Calendar]

// J'utilise régulièrement un préfixe pour les champs de mes différents calendriers (ici CD pour Canonical_Date, tu peux choisir autre chose de plus parlant pour toi)

[Calendar_Canonical]:

Load

  [Date]           As [Canonical_Date], // renommé le champ de cette façon permettra de faire un rapprochement avec la table [Datebridge]

  Date([Date],'DD/MM/YYYY')  AS [Date_CD],

  MakeWeekDate(Year([Date])-1, Week([Date]), Num(WeekDay([Date])))  AS [Day_Date_Y-1_CD], // Day Name Equiv N-1

  AddYears([Date],-1)        AS [Date_Y-1_CD], // Date Equiv N-1

  Date([Date],'DD')          AS [Num_Day_CD],

  Date([Date],'YYYYMMDD')    AS [FullDate_CD],

  Week([Date])               AS [Week_CD],

  WeekName([Date])           AS [Week_Name_CD],

  Year(Date)-1 & '/' & Num(Week(Date),'00')     AS [Week_Name_Y-1_CD],  // Num Semaine Equiv N-1

  Num(Week([Date]))          AS [Week_Num_CD],

  Month([Date])              AS [Month_CD],

  MonthName([Date])          AS [Month_Name_CD],

  Num(Month([Date]))         AS [Month_Num_CD],

  Year([Date])               AS [Year_CD],

  Year([Date]) & '.' & Week([Date])   AS [Year_Week_CD],

  Week([Date]) & '.' & Year([Date])   AS [Week_Year_CD],

  Num(Dual(Year([Date])&'-'&Week([Date]), weekstart([Date])))     AS [Year_Week_Num_CD],

  Year([Date]) & '.' & Month([Date])  AS [Year_Month_CD],

  Month([Date]) & '.' & Year([Date])  AS [Month_Year_CD],

  Num(Dual(Year([Date])&'-'&Month([Date]), monthstart([Date])))   AS [Year_Month_Num_CD],

  Dual('Q'&Num(Ceil(Num(Month([Date]))/3)),Num(Ceil(Num(Month([Date]))/3),00))  AS [Qarter_CD],

  Dual(Year([Date])&'-Q'&Num(Ceil(Num(Month([Date]))/3)),QuarterStart([Date]))         AS [Year_Quarter_CD]

Resident [tmp_Calendar]

;

Par contre pour être sur d'avoir un rapprochement correcte entre ton calendrier [Calendar_Canonical] et la table [DateBridge] vérifie que les champ [Canonical_Date] du calendrier et de la table [DateBridge] on le même format

[stephaniee]

Super! Et merci.

Encore une question (désolée)

: Comment je fais pour vérifier le format de mes dates comme tu le suggères ?

[stephaniee]

J'ai testé en reprenant tes calendriers proposés et j'ai un autre souci avec le message d'erreur suivant :

"Field names must be unique within table"

A priori il s'agit du calendrier 2 mais je ne trouve pas l'erreur. La vois-tu ?

// Calendrier sur la date [Date_Dossier] de la table [suivi] => Suivi_Date_Dossier

[MinMaxDates1]:

Load

    Min(Floor([Date_Dossier]))  AS [MinDate1],

    Max(Floor([Date_Dossier]))  AS [MaxDate1]

Resident [suivi]

;

Let vMinDate1 = Peek('MinDate1', 0, 'MinMaxDates1')*1;

Let vMaxDate1 = Peek('MaxDate1', 0, 'MinMaxDates1')*1;

Drop Table [MinMaxDates1];

[tmp_Calendar1]:

Load

  Date($(vMinDate1) + RowNo()-1) AS [Date1]

    AutoGenerate ($(vMaxDate1) - $(vMinDate1)+1)

    ;

// Calendrier du Suivis des Dates Dossier => Nom de table : [Calendar_SDD]  

[Calendar_SDD]:

Load

  [Date1]           As [Date_SDD],

  Date([Date1],'DD/MM/YYYY')  AS [Date__SDD],

  MakeWeekDate(Year([Date1])-1, Week([Date1]), Num(WeekDay([Date1])))  AS [Day_Date_Y-1__SDD], // Day Name Equiv N-1

  AddYears([Date1],-1)        AS [Date_Y-1__SDD], // Date Equiv N-1

  Date([Date1],'DD')          AS [Num_Day_SDD],

  Date([Date1],'YYYYMMDD')    AS [FullDate_SDD],

  Week([Date1])               AS [Week_SDD],

  WeekName([Date1])           AS [Week_Name_SDD],

  Year([Date1])-1 & '/' & Num(Week([Date1]),'00')     AS [Week_Name_Y-1_SDD],  // Num Semaine Equiv N-1

  Num(Week([Date1]))          AS [Week_Num_SDD],

  Month([Date1])              AS [Month_SDD],

  MonthName([Date1])          AS [Month_Name_SDD],

  Num(Month([Date1]))         AS [Month_Num_SDD],

  Year([Date1])               AS [Year_SDD],

  Year([Date1]) & '.' & Week([Date1])   AS [Year_Week_SDD],

  Week([Date1]) & '.' & Year([Date1])   AS [Week_Year_SDD],

  Num(Dual(Year([Date1])&'-'&Week([Date1]), weekstart([Date1])))     AS [Year_Week_Num_SDD],

  Year([Date1]) & '.' & Month([Date1])  AS [Year_Month_SDD],

  Month([Date1]) & '.' & Year([Date1])  AS [Month_Year_SDD],

  Num(Dual(Year([Date1])&'-'&Month([Date1]), monthstart([Date1])))   AS [Year_Month_Num_SDD],

  Dual('Q'&Num(Ceil(Num(Month([Date1]))/3)),Num(Ceil(Num(Month([Date1]))/3),00))  AS [Qarter_SDD],

  Dual(Year([Date1])&'-Q'&Num(Ceil(Num(Month([Date1]))/3)),QuarterStart([Date1]))         AS [Year_Quarter_SDD]

Resident [tmp_Calendar1]

;

Drop Tables [tmp_Calendar1];

/*************************************************************************************************************/

/*************************************************************************************************************/

// Calendrier sur la date [Date_Heure_Creation] de la table [gaq_suivi] => Suivi_Date_Réclamation

[MinMaxDates2]:

Load

    Min(Floor([Date_Heure_Creation]))  AS [MinDate2],

    Max(Floor([Date_Heure_Creation]))  AS [MaxDate2]

Resident [gaq_suivi]

;

Let vMinDate2 = Peek('MinDate2', 0, 'MinMaxDates2')*1;

Let vMaxDate2 = Peek('MaxDate2', 0, 'MinMaxDates2')*1;

Drop Table [MinMaxDates2];

[tmp_Calendar2]:

Load

  Date($(vMinDate2) + RowNo()-1) AS [Date2]

    AutoGenerate ($(vMaxDate2) - $(vMinDate2)+1)

    ;

// Calendrier du Suivis des Dates Réclamation => Nom de table : [Calendar_SDR]  

[Calendar_SDR]:

Load

  [Date2]           As [Date_SDR],

  Date([Date2],'DD/MM/YYYY')  AS [Date_SDR],

  MakeWeekDate(Year([Date2])-1, Week([Date2]), Num(WeekDay([Date2])))  AS [Day_Date_Y-1_SDR], // Day Name Equiv N-1

  AddYears([Date2],-1)        AS [Date_Y-1_SDR], // Date Equiv N-1

  Date([Date2],'DD')          AS [Num_Day_SDR],

  Date([Date2],'YYYYMMDD')    AS [FullDate_SDR],

  Week([Date2])               AS [Week_SDR],

  WeekName([Date2])           AS [Week_Name_SDR],

  Year([Date2])-1 & '/' & Num(Week([Date2]),'00')     AS [Week_Name_Y-1_SDR],  // Num Semaine Equiv N-1

  Num(Week([Date2]))          AS [Week_Num_SDR],

  Month([Date2])              AS [Month_SDR],

  MonthName([Date2])          AS [Month_Name_SDR],

  Num(Month([Date2]))         AS [Month_Num_SDR],

  Year([Date2])               AS [Year_SDR],

  Year([Date2]) & '.' & Week([Date2])   AS [Year_Week_SDR],

  Week([Date2]) & '.' & Year([Date2])   AS [Week_Year_SDR],

  Num(Dual(Year([Date2])&'-'&Week([Date2]), weekstart([Date2])))     AS [Year_Week_Num_SDR],

  Year([Date2]) & '.' & Month([Date2])  AS [Year_Month_SDR],

  Month([Date2]) & '.' & Year([Date2])  AS [Month_Year_SDR],

  Num(Dual(Year([Date2])&'-'&Month([Date2]), monthstart([Date2])))   AS [Year_Month_Num_SDR],

  Dual('Q'&Num(Ceil(Num(Month([Date2]))/3)),Num(Ceil(Num(Month([Date2]))/3),00))  AS [Qarter_SDR],

  Dual(Year([Date2])&'-Q'&Num(Ceil(Num(Month([Date2]))/3)),QuarterStart([Date2]))         AS [Year_Quarter_SDR]

Resident [tmp_Calendar2]

;

Drop Tables [tmp_Calendar2];

/*************************************************************************************************************/

/*************************************************************************************************************/

[Anonymous]

Dans une même table deux champs ne peuvent avoir le même nom, dans le deuxième calendrier les deux premiers champs ont été renommé avec un nom identique, du coup cela génère une erreur lors du chargement.

Essaye ce script, j'ai gardé volontairement toutes tes tables, par contre il y aura peut être un problème pour la table [suivi] dans le modèle de donnée.

Cette réponse intègre aussi la réponse de Sébastien du coup Sébastien devra peut être modifier un peu le script ci-dessous pour que l'ensemble soit cohérent.

[suivi]:

LOAD

[Num_Dossier]    As [Tmp_Num_Dossier],

[Date_Dossier],

Date(Floor([Date_Dossier]),'DD/MM/YYYY') AS [Date_Calendar_Date_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],

Date(Floor([Date_Heure_Creation]),'DD/MM/YYYY')    As [Date_Calendar_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`;

[DateBridge]:

Load

    Num_Dossier,

    Null() as Id_Reclamation,

    Date(Floor(Date_Dossier),'DD/MM/YYYY') as Canonical_Date, // Tu t'assure que la date est sur le format DD/MM/YYYY

    'Date_Dossier' as DateType

Resident suivi;

Concatenate (DateBridge)

Load

    Tmp_Num_Dossier as Num_Dossier,

    Id_Reclamation,

    Date(Floor(Date_Heure_Creation),'DD/MM/YYYY') as Canonical_Date, // Tu t'assure que la date est sur le format DD/MM/YYYY

    'Date_Reclamation' as DateType

Resident gaq_suivi;

Drop Field Tmp_Num_Dossier;

/*************************************************************************************************************/

/*************************************************************************************************************/

// Calendrier sur la date [Date_Dossier] de la table [suivi] => Suivi_Date_Dossier

[MinMaxDates1]:

Load

    Min(Floor([Date_Dossier]))  AS [MinDate1],

    Max(Floor([Date_Dossier]))  AS [MaxDate1]

Resident [suivi]

;

Let vMinDate1 = Peek('MinDate1', 0, 'MinMaxDates1')*1;

Let vMaxDate1 = Peek('MaxDate1', 0, 'MinMaxDates1')*1;

Drop Table [MinMaxDates1];

[tmp_Calendar1]:

Load

  Date($(vMinDate1) + RowNo()-1) AS [Date1]

  AutoGenerate ($(vMaxDate1) - $(vMinDate1)+1)

    ;

// Calendrier du Suivis des Dates Dossier => Nom de table : [Calendar_SDD] 

[Calendar_SDD]:

Load

  [Date1]           As [Date_Calendar_Date_Dossier], // Renommé le champ permettra le rapprochement entre le calendrier et la table [suivi]

  Date([Date1],'DD/MM/YYYY')  AS [Date_SDD],

  MakeWeekDate(Year([Date1])-1, Week([Date1]), Num(WeekDay([Date1])))  AS [Day_Date_Y-1_SDD], // Day Name Equiv N-1

  AddYears([Date1],-1)        AS [Date_Y-1_SDD], // Date Equiv N-1

  Date([Date1],'DD')          AS [Num_Day_SDD],

  Date([Date1],'YYYYMMDD')    AS [FullDate_SDD],

  Week([Date1])               AS [Week_SDD],

  WeekName([Date1])           AS [Week_Name_SDD],

  Year([Date1])-1 & '/' & Num(Week([Date1]),'00')     AS [Week_Name_Y-1_SDD],  // Num Semaine Equiv N-1

  Num(Week([Date1]))          AS [Week_Num_SDD],

  Month([Date1])              AS [Month_SDD],

  MonthName([Date1])          AS [Month_Name_SDD],

  Num(Month([Date1]))         AS [Month_Num_SDD],

  Year([Date1])               AS [Year_SDD],

  Year([Date1]) & '.' & Week([Date1])   AS [Year_Week_SDD],

  Week([Date1]) & '.' & Year([Date1])   AS [Week_Year_SDD],

  Num(Dual(Year([Date1])&'-'&Week([Date1]), weekstart([Date1])))     AS [Year_Week_Num_SDD],

  Year([Date1]) & '.' & Month([Date1])  AS [Year_Month_SDD],

  Month([Date1]) & '.' & Year([Date1])  AS [Month_Year_SDD],

  Num(Dual(Year([Date1])&'-'&Month([Date1]), monthstart([Date1])))   AS [Year_Month_Num_SDD],

  Dual('Q'&Num(Ceil(Num(Month([Date1]))/3)),Num(Ceil(Num(Month([Date1]))/3),00))  AS [Qarter_SDD],

  Dual(Year([Date1])&'-Q'&Num(Ceil(Num(Month([Date1]))/3)),QuarterStart([Date1]))         AS [Year_Quarter_SDD]

Resident [tmp_Calendar1]

;

Drop Tables [tmp_Calendar1];

/*************************************************************************************************************/

/*************************************************************************************************************/

// Calendrier sur les dates [Date_Heure_Creation] de la table [gaq_suivi] => Suivi_Date_Réclamation

[MinMaxDates2]:

Load

    Min(Floor([Date_Heure_Creation]))  AS [MinDate2],

    Max(Floor([Date_Heure_Creation]))  AS [MaxDate2]

Resident [gaq_suivi]

;

Let vMinDate2 = Peek('MinDate2', 0, 'MinMaxDates2')*1;

Let vMaxDate2 = Peek('MaxDate2', 0, 'MinMaxDates2')*1;

Drop Table [MinMaxDates2];

[tmp_Calendar2]:

Load

  Date($(vMinDate2) + RowNo()-1) AS [Date2]

  AutoGenerate ($(vMaxDate2) - $(vMinDate2)+1)

    ;

// Calendrier sur les dates Dates Réclamation => Nom de table : [Calendar_SDR] 

[Calendar_SDR]:

Load

  [Date2]           As [Date_Calendar_Date_Heure_Creation], // Renommé le champ permettra le rapprochement entre le calendrier et la table [gaq_suivi]

  Date([Date2],'DD/MM/YYYY')  AS [Date_SDR],

  MakeWeekDate(Year([Date2])-1, Week([Date2]), Num(WeekDay([Date2])))  AS [Day_Date_Y-1_SDR], // Day Name Equiv N-1

  AddYears([Date2],-1)        AS [Date_Y-1_SDR], // Date Equiv N-1

  Date([Date2],'DD')          AS [Num_Day_SDR],

  Date([Date2],'YYYYMMDD')    AS [FullDate_SDR],

  Week([Date2])               AS [Week_SDR],

  WeekName([Date2])           AS [Week_Name_SDR],

  Year([Date2])-1 & '/' & Num(Week([Date2]),'00')     AS [Week_Name_Y-1_SDR],  // Num Semaine Equiv N-1

  Num(Week([Date2]))          AS [Week_Num_SDR],

  Month([Date2])              AS [Month_SDR],

  MonthName([Date2])          AS [Month_Name_SDR],

  Num(Month([Date2]))         AS [Month_Num_SDR],

  Year([Date2])               AS [Year_SDR],

  Year([Date2]) & '.' & Week([Date2])   AS [Year_Week_SDR],

  Week([Date2]) & '.' & Year([Date2])   AS [Week_Year_SDR],

  Num(Dual(Year([Date2])&'-'&Week([Date2]), weekstart([Date2])))     AS [Year_Week_Num_SDR],

  Year([Date2]) & '.' & Month([Date2])  AS [Year_Month_SDR],

  Month([Date2]) & '.' & Year([Date2])  AS [Month_Year_SDR],

  Num(Dual(Year([Date2])&'-'&Month([Date2]), monthstart([Date2])))   AS [Year_Month_Num_SDR],

  Dual('Q'&Num(Ceil(Num(Month([Date2]))/3)),Num(Ceil(Num(Month([Date2]))/3),00))  AS [Qarter_SDR],

  Dual(Year([Date2])&'-Q'&Num(Ceil(Num(Month([Date2]))/3)),QuarterStart([Date2]))         AS [Year_Quarter_SDR]

Resident [tmp_Calendar2]

;

Drop Tables [tmp_Calendar2];

/*************************************************************************************************************/

/*************************************************************************************************************/

// Calendrier sur la date [Canonical_Date] de la table [DateBridge]

[MinMaxDates2]:

Load

    Min(Floor([Canonical_Date]))  AS [MinDate3],

    Max(Floor([Canonical_Date]))  AS [MaxDate3]

Resident [DateBridge]

;

Let vMinDate3 = Peek('MinDate3', 0, 'MinMaxDates3')*1;

Let vMaxDate3 = Peek('MaxDate3', 0, 'MinMaxDates3')*1;

Drop Table [MinMaxDates3];

[tmp_Calendar3]:

Load

  Date($(vMinDate3) + RowNo()-1) AS [Date3]

  AutoGenerate ($(vMaxDate3) - $(vMinDate3)+1)

    ;

// Calendrier sur les date [Canonical_Date] => Nom de table : [Calendar_DB] 

[Calendar_DB]:

Load

  [Date3]           As [Canonical_Date], // Renommé le champ permettra le rapprochement entre le calendrier et la table [gaq_suivi]

  Date([Date3],'DD/MM/YYYY')  AS [Date_DB],

  MakeWeekDate(Year([Date3])-1, Week([Date2]), Num(WeekDay([Date2])))  AS [Day_Date_Y-1_DB], // Day Name Equiv N-1

  AddYears([Date3],-1)        AS [Date_Y-1_DB], // Date Equiv N-1

  Date([Date3],'DD')          AS [Num_Day_DB],

  Date([Date3],'YYYYMMDD')    AS [FullDate_DB],

  Week([Date3])               AS [Week_DB],

  WeekName([Date3])           AS [Week_Name_DB],

  Year([Date3])-1 & '/' & Num(Week([Date3]),'00')     AS [Week_Name_Y-1_DB],  // Num Semaine Equiv N-1

  Num(Week([Date3]))          AS [Week_Num_DB],

  Month([Date3])              AS [Month_DB],

  MonthName([Date3])          AS [Month_Name_DB],

  Num(Month([Date3]))         AS [Month_Num_DB],

  Year([Date3])               AS [Year_DB],

  Year([Date3]) & '.' & Week([Date3])   AS [Year_Week_DB],

  Week([Date3]) & '.' & Year([Date3])   AS [Week_Year_DB],

  Num(Dual(Year([Date3])&'-'&Week([Date3]), weekstart([Date3])))     AS [Year_Week_Num_DB],

  Year([Date3]) & '.' & Month([Date3])  AS [Year_Month_DB],

  Month([Date3]) & '.' & Year([Date3])  AS [Month_Year_DB],

  Num(Dual(Year([Date3])&'-'&Month([Date3]), monthstart([Date3])))   AS [Year_Month_Num_DB],

  Dual('Q'&Num(Ceil(Num(Month([Date3]))/3)),Num(Ceil(Num(Month([Date3]))/3),00))  AS [Qarter_DB],

  Dual(Year([Date3])&'-Q'&Num(Ceil(Num(Month([Date3]))/3)),QuarterStart([Date3]))         AS [Year_Quarter_DB]

Resident [tmp_Calendar3]

;

Drop Tables [tmp_Calendar3];

[sfatoux72]

C'est dans la table gap_suivi qu'il faut renommer le champ Num_Dossier en Tmp_Num_Dossier

Normalement tu n'as pas besoin des 3 calendriers, le dernier sur Canonical_Date devrait suffire. Mais je te conseille de garder le 2 premier temporairement pour que tu puisse essayer de réaliser tes expressions avec les 2 premiers calendriers et avec le dernier pour t'exercer. La plupart des expressions pourront être réalisées des 2 façon , mais certaines nécessiteront l'utilisation de Canonical_Date

[suivi]:

LOAD

[Num_Dossier],

[Date_Dossier],

Date(Floor([Date_Dossier]),'DD/MM/YYYY') AS [Date_Calendar_Date_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]    As [Tmp_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],

Date(Floor([Date_Heure_Creation]),'DD/MM/YYYY')    As [Date_Calendar_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`;

[DateBridge]:

Load

    Num_Dossier,

    Null() as Id_Reclamation,

    Date(Floor(Date_Dossier),'DD/MM/YYYY') as Canonical_Date, // Tu t'assure que la date est sur le format DD/MM/YYYY

    'Date_Dossier' as DateType

Resident suivi;

Concatenate (DateBridge)

Load

    Tmp_Num_Dossier as Num_Dossier,

    Id_Reclamation,

    Date(Floor(Date_Heure_Creation),'DD/MM/YYYY') as Canonical_Date, // Tu t'assure que la date est sur le format DD/MM/YYYY

    'Date_Reclamation' as DateType

Resident gaq_suivi;

Drop Field Tmp_Num_Dossier;

/*************************************************************************************************************/

/*************************************************************************************************************/

// Calendrier sur la date [Date_Dossier] de la table [suivi] => Suivi_Date_Dossier

[MinMaxDates1]:

Load

    Min(Floor([Date_Dossier]))  AS [MinDate1],

    Max(Floor([Date_Dossier]))  AS [MaxDate1]

Resident [suivi]

;

Let vMinDate1 = Peek('MinDate1', 0, 'MinMaxDates1')*1;

Let vMaxDate1 = Peek('MaxDate1', 0, 'MinMaxDates1')*1;

Drop Table [MinMaxDates1];

[tmp_Calendar1]:

Load

  Date($(vMinDate1) + RowNo()-1) AS [Date1]

  AutoGenerate ($(vMaxDate1) - $(vMinDate1)+1)

    ;

// Calendrier du Suivis des Dates Dossier => Nom de table : [Calendar_SDD]

[Calendar_SDD]:

Load

  [Date1]           As [Date_Calendar_Date_Dossier], // Renommé le champ permettra le rapprochement entre le calendrier et la table [suivi]

  Date([Date1],'DD/MM/YYYY')  AS [Date_SDD],

  MakeWeekDate(Year([Date1])-1, Week([Date1]), Num(WeekDay([Date1])))  AS [Day_Date_Y-1_SDD], // Day Name Equiv N-1

  AddYears([Date1],-1)        AS [Date_Y-1_SDD], // Date Equiv N-1

  Date([Date1],'DD')          AS [Num_Day_SDD],

  Date([Date1],'YYYYMMDD')    AS [FullDate_SDD],

  Week([Date1])               AS [Week_SDD],

  WeekName([Date1])           AS [Week_Name_SDD],

  Year([Date1])-1 & '/' & Num(Week([Date1]),'00')     AS [Week_Name_Y-1_SDD],  // Num Semaine Equiv N-1

  Num(Week([Date1]))          AS [Week_Num_SDD],

  Month([Date1])              AS [Month_SDD],

  MonthName([Date1])          AS [Month_Name_SDD],

  Num(Month([Date1]))         AS [Month_Num_SDD],

  Year([Date1])               AS [Year_SDD],

  Year([Date1]) & '.' & Week([Date1])   AS [Year_Week_SDD],

  Week([Date1]) & '.' & Year([Date1])   AS [Week_Year_SDD],

  Num(Dual(Year([Date1])&'-'&Week([Date1]), weekstart([Date1])))     AS [Year_Week_Num_SDD],

  Year([Date1]) & '.' & Month([Date1])  AS [Year_Month_SDD],

  Month([Date1]) & '.' & Year([Date1])  AS [Month_Year_SDD],

  Num(Dual(Year([Date1])&'-'&Month([Date1]), monthstart([Date1])))   AS [Year_Month_Num_SDD],

  Dual('Q'&Num(Ceil(Num(Month([Date1]))/3)),Num(Ceil(Num(Month([Date1]))/3),00))  AS [Qarter_SDD],

  Dual(Year([Date1])&'-Q'&Num(Ceil(Num(Month([Date1]))/3)),QuarterStart([Date1]))         AS [Year_Quarter_SDD]

Resident [tmp_Calendar1]

;

Drop Tables [tmp_Calendar1];

/*************************************************************************************************************/

/*************************************************************************************************************/

// Calendrier sur les dates [Date_Heure_Creation] de la table [gaq_suivi] => Suivi_Date_Réclamation

[MinMaxDates2]:

Load

    Min(Floor([Date_Heure_Creation]))  AS [MinDate2],

    Max(Floor([Date_Heure_Creation]))  AS [MaxDate2]

Resident [gaq_suivi]

;

Let vMinDate2 = Peek('MinDate2', 0, 'MinMaxDates2')*1;

Let vMaxDate2 = Peek('MaxDate2', 0, 'MinMaxDates2')*1;

Drop Table [MinMaxDates2];

[tmp_Calendar2]:

Load

  Date($(vMinDate2) + RowNo()-1) AS [Date2]

  AutoGenerate ($(vMaxDate2) - $(vMinDate2)+1)

    ;

// Calendrier sur les dates Dates Réclamation => Nom de table : [Calendar_SDR]

[Calendar_SDR]:

Load

  [Date2]           As [Date_Calendar_Date_Heure_Creation], // Renommé le champ permettra le rapprochement entre le calendrier et la table [gaq_suivi]

  Date([Date2],'DD/MM/YYYY')  AS [Date_SDR],

  MakeWeekDate(Year([Date2])-1, Week([Date2]), Num(WeekDay([Date2])))  AS [Day_Date_Y-1_SDR], // Day Name Equiv N-1

  AddYears([Date2],-1)        AS [Date_Y-1_SDR], // Date Equiv N-1

  Date([Date2],'DD')          AS [Num_Day_SDR],

  Date([Date2],'YYYYMMDD')    AS [FullDate_SDR],

  Week([Date2])               AS [Week_SDR],

  WeekName([Date2])           AS [Week_Name_SDR],

  Year([Date2])-1 & '/' & Num(Week([Date2]),'00')     AS [Week_Name_Y-1_SDR],  // Num Semaine Equiv N-1

  Num(Week([Date2]))          AS [Week_Num_SDR],

  Month([Date2])              AS [Month_SDR],

  MonthName([Date2])          AS [Month_Name_SDR],

  Num(Month([Date2]))         AS [Month_Num_SDR],

  Year([Date2])               AS [Year_SDR],

  Year([Date2]) & '.' & Week([Date2])   AS [Year_Week_SDR],

  Week([Date2]) & '.' & Year([Date2])   AS [Week_Year_SDR],

  Num(Dual(Year([Date2])&'-'&Week([Date2]), weekstart([Date2])))     AS [Year_Week_Num_SDR],

  Year([Date2]) & '.' & Month([Date2])  AS [Year_Month_SDR],

  Month([Date2]) & '.' & Year([Date2])  AS [Month_Year_SDR],

  Num(Dual(Year([Date2])&'-'&Month([Date2]), monthstart([Date2])))   AS [Year_Month_Num_SDR],

  Dual('Q'&Num(Ceil(Num(Month([Date2]))/3)),Num(Ceil(Num(Month([Date2]))/3),00))  AS [Qarter_SDR],

  Dual(Year([Date2])&'-Q'&Num(Ceil(Num(Month([Date2]))/3)),QuarterStart([Date2]))         AS [Year_Quarter_SDR]

Resident [tmp_Calendar2]

;

Drop Tables [tmp_Calendar2];

/*************************************************************************************************************/

/*************************************************************************************************************/

// Calendrier sur la date [Canonical_Date] de la table [DateBridge]

[MinMaxDates2]:

Load

    Min(Floor([Canonical_Date]))  AS [MinDate3],

    Max(Floor([Canonical_Date]))  AS [MaxDate3]

Resident [DateBridge]

;

Let vMinDate3 = Peek('MinDate3', 0, 'MinMaxDates3')*1;

Let vMaxDate3 = Peek('MaxDate3', 0, 'MinMaxDates3')*1;

Drop Table [MinMaxDates3];

[tmp_Calendar3]:

Load

  Date($(vMinDate3) + RowNo()-1) AS [Date3]

  AutoGenerate ($(vMaxDate3) - $(vMinDate3)+1)

    ;

// Calendrier sur les date [Canonical_Date] => Nom de table : [Calendar_DB]

[Calendar_DB]:

Load

  [Date3]           As [Canonical_Date], // Renommé le champ permettra le rapprochement entre le calendrier et la table [gaq_suivi]

  Date([Date3],'DD/MM/YYYY')  AS [Date_DB],

  MakeWeekDate(Year([Date3])-1, Week([Date2]), Num(WeekDay([Date2])))  AS [Day_Date_Y-1_DB], // Day Name Equiv N-1

  AddYears([Date3],-1)        AS [Date_Y-1_DB], // Date Equiv N-1

  Date([Date3],'DD')          AS [Num_Day_DB],

  Date([Date3],'YYYYMMDD')    AS [FullDate_DB],

  Week([Date3])               AS [Week_DB],

  WeekName([Date3])           AS [Week_Name_DB],

  Year([Date3])-1 & '/' & Num(Week([Date3]),'00')     AS [Week_Name_Y-1_DB],  // Num Semaine Equiv N-1

  Num(Week([Date3]))          AS [Week_Num_DB],

  Month([Date3])              AS [Month_DB],

  MonthName([Date3])          AS [Month_Name_DB],

  Num(Month([Date3]))         AS [Month_Num_DB],

  Year([Date3])               AS [Year_DB],

  Year([Date3]) & '.' & Week([Date3])   AS [Year_Week_DB],

  Week([Date3]) & '.' & Year([Date3])   AS [Week_Year_DB],

  Num(Dual(Year([Date3])&'-'&Week([Date3]), weekstart([Date3])))     AS [Year_Week_Num_DB],

  Year([Date3]) & '.' & Month([Date3])  AS [Year_Month_DB],

  Month([Date3]) & '.' & Year([Date3])  AS [Month_Year_DB],

  Num(Dual(Year([Date3])&'-'&Month([Date3]), monthstart([Date3])))   AS [Year_Month_Num_DB],

  Dual('Q'&Num(Ceil(Num(Month([Date3]))/3)),Num(Ceil(Num(Month([Date3]))/3),00))  AS [Qarter_DB],

  Dual(Year([Date3])&'-Q'&Num(Ceil(Num(Month([Date3]))/3)),QuarterStart([Date3]))         AS [Year_Quarter_DB]

Resident [tmp_Calendar3]

;

Drop Tables [tmp_Calendar3];

[Anonymous]

Bonjour Sébastien,

Autant pour moi en effet, j'ai lu ta réponse un peu rapidement, et donc je me suis trompé de table (suivi à la place de gaq_suivi) et en plus j'ai recopié ta solution bêtement sans me rendre compte de mon erreur

@Stéphanie tu dois prendre directement le script dans la réponse de Sébastien, le mien comporte des erreurs

@Sébastien pourrais tu me donner un exemple d'expression nécessitant uniquement le calendrier Canonique, je suis curieux de savoir quel type de demande pourrait nécessiter un tel calendrier