Re: Problème boucle for - Qlik Community - 1299432

Skip to main content

Qlik.com | Qlik Help | Resources

Ask a Question

Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!

Bonjour.

J'ai crée une liste de jours fériés dynamique en fonction des dates chargées:

Let listeFeries = Null();

Set DateDebut = =Year(Min($(CreationDate_Field)));

Set DateFin = =Year(Max($(CreationDate_Field)));

For Annee=DateDebut To 2017

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 1, 1) & chr(39); // Jour de l'an

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 5, 1) & chr(39); // Fete du travail 1er Mai

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 5, 😎 & chr(39); // Armistice 1945 8 Mai

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 7, 14) & chr(39); // Fete National 14 Juillet

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 8, 15) & chr(39); // Assomption 15 Aout

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 11, 1) & chr(39); // Toussaint 1er Novembre

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 11, 11) & chr(39); // Armistice 1918 11 Novembre

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 12, 25) & chr(39); // Noel

DatePaques = Date(Round(makedate($(Annee),4,day(Minute($(Annee)/38)/2+55))/7)*7-6); // Dimanche de Paques

listeFeries=listeFeries & ',' & chr(39) & DatePaques & chr(39); // Dimanche de Paques

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 1), 'DD/MM/YYYY') & chr(39); // Lundi de Paques

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 39), 'DD/MM/YYYY') & chr(39); // Jeudi de l'Ascension

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 49), 'DD/MM/YYYY') & chr(39); // Dimanche de Pentecote

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 50), 'DD/MM/YYYY') & chr(39); // Lundi de Pentecote

Next

Let listeFeries = Mid(vlisteFeries,2);

let DatePaques = Null();

Let Annee = Null();

Ce script marche très bien, mais comme vous le voyez, j'ai saisi la date 2017 en dur dans la boucle for. Je ne comprends pas pourquoi ceci: For Annee=DateDebut To DateFin , me renvoie une erreur:

Syntax error

Unexpected token: '1', expected nothing

listeFeries=listeFeries & ',' & chr(39) & makedate(-, >>>>>>1<<<<<<, 1) & chr(39)

Du coup je ne peux pas avoir de borne supérieure dynamique ce qui m’embête.

Cordialement

30 Replies

Author

Bonjour,

Je viens de le tester sur le calendrier de mon appli, ca tourne bien :

Let listeFeries = Null();

MinMaxDate:

load

min(year(date_id)) as MinDate,

max(year(date_id)) as MaxDate

Resident price;

Let vMinDate = Peek('MinDate',0,'MinMaxDate');

Let vMaxDate = Peek('MaxDate',0,'MinMaxDate');

For Annee= $(vMinDate) To $(vMaxDate)

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 1, 1) & chr(39); // Jour de l'an

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 5, 1) & chr(39); // Fete du travail 1er Mai

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 5, 😎 & chr(39); // Armistice 1945 8 Mai

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 7, 14) & chr(39); // Fete National 14 Juillet

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 8, 15) & chr(39); // Assomption 15 Aout

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 11, 1) & chr(39); // Toussaint 1er Novembre

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 11, 11) & chr(39); // Armistice 1918 11 Novembre

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 12, 25) & chr(39); // Noel

DatePaques = Date(Round(makedate($(Annee),4,day(Minute($(Annee)/38)/2+55))/7)*7-6); // Dimanche de Paques

listeFeries=listeFeries & ',' & chr(39) & DatePaques & chr(39); // Dimanche de Paques

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 1), 'DD/MM/YYYY') & chr(39); // Lundi de Paques

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 39), 'DD/MM/YYYY') & chr(39); // Jeudi de l'Ascension

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 49), 'DD/MM/YYYY') & chr(39); // Dimanche de Pentecote

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 50), 'DD/MM/YYYY') & chr(39); // Lundi de Pentecote

Next

Let listeFeries = Mid(listeFeries,2);

let DatePaques = Null();

Let Annee = Null();

Author

Bonjour Jean Eude et merci pour ta réponse.

Je ne peux pas faire comme toi car je ne charge les tables que plus tard dans le script.

De plus, j'aimerai quand même comprendre pourquoi cette syntaxe ne fonctionne pas. Ou plutôt, pourquoi elle fonctionne avec la borne inférieure et pas la borne supérieure.

si ça: For Annee=DateDebut To 2017 fonctionne,

ça: For Annee=DateDebut To DateFin devrait marcher.

Author

Si vous lancez le script en mode debug, au moment de passer sur la premiére ligne de votre boucle for; que contient la ligne du for Annee=... ?

Author

Alors en fait après vérification,

Set DateDebut = Year(Min($(CreationDate_Field)));

Set DateFin = Year(Max($(CreationDate_Field)));

Ceci ne me ramène pas l'année.

Quelle serait la bonne syntaxe ?

Author

Que contient CreationDate_Field ? Il s'agit d'un champs d'une table ? d'une variable ? d'un fichier ?

Author

DateFin par exemple = Year(Max([Date de création]))

Oui c'est un champ

Author

Je ne comprend pas pourquoi c'est pas possible de faire comme ceci :

MinMaxDate:

load

min(year([Date de création])) as MinDate,

max(year([Date de création])) as MaxDate

Resident votreTable;

Let vMinDate = Peek('MinDate',0,'MinMaxDate');

Let vMaxDate = Peek('MaxDate',0,'MinMaxDate');

For Annee= $(vMinDate) To $(vMaxDate)

C'est possible d'avoir votre application, ou du moins le script ?

Author

Voici mon script:

/**

*======================================================================================================

* Author: Bertrand LANTUEJOUL

*======================================================================================================

*/

/**

*======================================================================================================

*======================================================================================================

* SUMMARY

*------------------------------------------------------------------------------------------------------

* Set general variables

* Database connection

* Set Variables

* Table

* Date Key

* CreationDate

* ExitDate

* Temporal dimensions

* Dimensions

* Labels

* Numbers

* Temporal granularity

* Delay

* Maturity

* Duration

* Entities

* State

* Math

* Controls

* KeyDate

* States

* Delays and durations

* Others

* Various

* Date handling

* Datamart Request

* Cleaning Fields

* Formating

* Calendrier de création

* Calendrier de traitement

* Delete unuse variables

*------------------------------------------------------------------------------------------------------

* STRUCTURE

*------------------------------------------------------------------------------------------------------

* Calendrier de création

* Calendrier de traitement

* Cartes

*======================================================================================================

*======================================================================================================

*/

/*

*======================================================================================================

* Set general variables

*======================================================================================================

*/

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET CollationLocale='fr-FR';

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET LongMonthNames='janvier;février;mars;avril;mai;juin;juillet;août;septembre;octobre;novembre;décembre';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

SET LongDayNames='lundi;mardi;mercredi;jeudi;vendredi;samedi;dimanche';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET FirstWeekDay=0; // 0 = Monday

SET BrokenWeeks=0;

SET ReferenceDay=4; // 4 = Défault. The 4nt of January must be in the first week

SET FirstMonthOfYear=1; // 1 = January

/*

*======================================================================================================

* Database connection

*======================================================================================================

*/

/*

*======================================================================================================

* Set Variables

*======================================================================================================

*/

/*

* Tables

*------------------------------------------------------------------------------------------------------

*/

Set TableName_Source='"MaTable"'; // Name of the table source on database source

Set TableName_Renamed="Cartes"; // Name of the table in QlikView relational model

/*

* Date key

*------------------------------------------------------------------------------------------------------

*/

Set DateList_Source = 'DateFlux', 'DATE_CREATION_PDF', 'DATE_MISE_SOUS_PLI', 'DATE_IMPRESSION', 'DATE_POSTAGE', 'DATE_ANNULATION'; // List of the Date Key source name in DataBase

Set DateList_Key_Choice = 'Création;PDF;Mise sous pli;Impression;Postage;Annulation'; // This list appears on DateKey field choice possibilities

Set CreationDateKey_Name = 'Création'; // Name of the Creation Date for that or those entity(ies)

Set ExitDateKey_Name = 'Postage'; // Name of the Exit Date for that or those entity(ies)

// Creation Date

Set CreationDate_Source = DateFlux; // Name of the Creation Date field source on the DataBase

Set CreationDate_Genitive = 'de création'; // Genitive of the Creation Date

Set CreationDate_Field = '[Date $(CreationDate_Genitive)]'; // Name of the Creation Date field on QlikView

// Exit Date

Set ExitDate_Source = DATE_POSTAGE; // Name of the Exit Date field source on the DataBase

Set ExitDate_Genitive = 'de postage'; // Genitive of the Exit Date

Set ExitDate_Field = '[Date $(ExitDate_Genitive)]'; // Name of the Exit Date field on QlikView

Set DateKey_Value =

= if( DateKey='$(CreationDateKey_Name)', '$(CreationDate_Field)',

if( DateKey='PDF', '[Date de création du PDF]',

if( DateKey='Mise sous pli', '[Date de mise sous pli]',

if( DateKey='Impression', '[Date d''impression]',

if( DateKey='Annulation', '[Date d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '$(ExitDate_Field)'

))))));

Set DateKey_Participle =

= if( DateKey='$(CreationDateKey_Name)', 'créé',

if( DateKey='PDF', 'créé (PDF)',

if( DateKey='Mise sous pli', 'mis sous pli',

if( DateKey='Impression', 'imprimé',

if( DateKey='Annulation', 'annulé',

if( DateKey='$(ExitDateKey_Name)', 'posté'

))))));

Set DateKey_Participle_InTune =

= if( DateKey='$(CreationDateKey_Name)', 'créées',

if( DateKey='PDF', 'créées (PDF)',

if( DateKey='Mise sous pli', 'mises sous pli',

if( DateKey='Impression', 'imprimées',

if( DateKey='Annulation', 'annulées',

if( DateKey='$(ExitDateKey_Name)', 'postéées'

))))));

Set DateKey_Participle_InTune_Singular =

= if( DateKey='$(CreationDateKey_Name)', 'créée',

if( DateKey='PDF', 'créée (PDF)',

if( DateKey='Mise sous pli', 'mise sous pli',

if( DateKey='Impression', 'imprimée',

if( DateKey='Annulation', 'annulée',

if( DateKey='$(ExitDateKey_Name)', 'postéée'

))))));

Set DateKey_Particle =

= if( DateKey='$(CreationDateKey_Name)', '$(CreationDate_Genitive)',

if( DateKey='PDF', 'de création (PDF)',

if( DateKey='Mise sous pli', 'de mise sous pli',

if( DateKey='Impression', 'd''impression',

if( DateKey='Annulation', 'd''annulation',

if( DateKey='$(ExitDateKey_Name)', '$(ExitDate_Genitive)'

))))));

/*

* Dimensions

* Use as dimensions on graphs and others

*/

Set DateKey_WeekDay_Dimension =

= if( DateKey='$(CreationDateKey_Name)', '[Jour de la semaine $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Jour de la semaine de création du PDF]',

if( DateKey='Mise sous pli', '[Jour de la semaine de mise sous pli]',

if( DateKey='Impression', '[Jour de la semaine d''impression]',

if( DateKey='Annulation', '[Jour de la semaine d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Jour de la semaine $(ExitDate_Genitive)]'))))));

Set DateKey_Week_Dimension =

= if( DateKey='$(CreationDateKey_Name)', '[Semaine $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Semaine de création du PDF]',

if( DateKey='Mise sous pli', '[Semaine de mise sous pli]',

if( DateKey='Impression', '[Semaine d''impression]',

if( DateKey='Annulation', '[Semaine d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Semaine $(ExitDate_Genitive)]'))))));

Set DateKey_Mouth_Dimension =

= if( DateKey='$(CreationDateKey_Name)', '[Mois $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Mois de création du PDF]',

if( DateKey='Mise sous pli', '[Mois de mise sous pli]',

if( DateKey='Impression', '[Mois d''impression]',

if( DateKey='Annulation', '[Mois d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Mois $(ExitDate_Genitive)]'))))));

Set DateKey_Quarter_Dimension =

= if( DateKey='$(CreationDateKey_Name)', '[Trimestre $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Trimestre de création du PDF]',

if( DateKey='Mise sous pli', '[Trimestre de mise sous pli]',

if( DateKey='Impression', '[Trimestre d''impression]',

if( DateKey='Annulation', '[Trimestre d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Trimestre $(ExitDate_Genitive)]'))))));

Set DateKey_Year_Dimension =

= if( DateKey='$(CreationDateKey_Name)', '[Année $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Année de création du PDF]',

if( DateKey='Mise sous pli', '[Année de mise sous pli]',

if( DateKey='Impression', '[Année d''impression]',

if( DateKey='Annulation', '[Année d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Année $(ExitDate_Genitive)]'))))));

/*

* Labels

* Use as textual description of the value of the dimensions

*/

Set DateKey_Day_Label =

= if( DateKey='$(CreationDateKey_Name)', '[Libellé date $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Libellé date de création du PDF]',

if( DateKey='Mise sous pli', '[Libellé date de mise sous pli]',

if( DateKey='Impression', '[Libellé date d''impression]',

if( DateKey='Annulation', '[Libellé date d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Libellé date $(ExitDate_Genitive)]'))))));

Set DateKey_WeekDay_Label =

= if( DateKey='$(CreationDateKey_Name)', '[Libellé jour de la semaine $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Libellé jour de la semaine de création du PDF]',

if( DateKey='Mise sous pli', '[Libellé jour de la semaine de mise sous pli]',

if( DateKey='Impression', '[Libellé jour de la semaine d''impression]',

if( DateKey='Annulation', '[Libellé jour de la semaine d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Libellé jour de la semaine $(ExitDate_Genitive)]'))))));

Set DateKey_Week_Label =

= if( DateKey='$(CreationDateKey_Name)', '[Libellé semaine $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Libellé semaine de création du PDF]',

if( DateKey='Mise sous pli', '[Libellé semaine de mise sous pli]',

if( DateKey='Impression', '[Libellé semaine d''impression]',

if( DateKey='Annulation', '[Libellé semaine d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Libellé semaine $(ExitDate_Genitive)]'))))));

Set DateKey_Month_Label =

= if( DateKey='$(CreationDateKey_Name)', '[Libellé mois $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Libellé mois de création du PDF]',

if( DateKey='Mise sous pli', '[Libellé mois de mise sous pli]',

if( DateKey='Impression', '[Libellé mois d''impression]',

if( DateKey='Annulation', '[Libellé mois d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Libellé mois $(ExitDate_Genitive)]'))))));

Set DateKey_Quarter_Label =

= if( DateKey='$(CreationDateKey_Name)', '[Libellé trimestre $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Libellé trimestre de création du PDF]',

if( DateKey='Mise sous pli', '[Libellé trimestre de mise sous pli]',

if( DateKey='Impression', '[Libellé trimestre d''impression]',

if( DateKey='Annulation', '[Libellé trimestre d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Libellé trimestre $(ExitDate_Genitive)]'))))));

Set DateKey_Year_Label =

= if( DateKey='$(CreationDateKey_Name)', '[Libellé année $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Libellé année de création du PDF]',

if( DateKey='Mise sous pli', '[Libellé année de mise sous pli]',

if( DateKey='Impression', '[Libellé année d''impression]',

if( DateKey='Annulation', '[Libellé année d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Libellé année $(ExitDate_Genitive)]'))))));

/*

* Numbers

* Use to get a continuous range of date. (Use for the steering coefficient)

*/

Set DateKey_Week_Number =

= if( DateKey='$(CreationDateKey_Name)', '[Numéro semaine $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Numéro semaine de création du PDF]',

if( DateKey='Mise sous pli', '[Numéro semaine de mise sous pli]',

if( DateKey='Impression', '[Numéro semaine d''impression]',

if( DateKey='Annulation', '[Numéro semaine d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Numéro semaine $(ExitDate_Genitive)]'))))));

Set DateKey_Month_Number =

= if( DateKey='$(CreationDateKey_Name)', '[Numéro mois $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Numéro mois de création du PDF]',

if( DateKey='Mise sous pli', '[Numéro mois de mise sous pli]',

if( DateKey='Impression', '[Numéro mois d''impression]',

if( DateKey='Annulation', '[Numéro mois d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Numéro mois $(ExitDate_Genitive)]'))))));

Set DateKey_Quarter_Number =

= if( DateKey='$(CreationDateKey_Name)', '[Numéro trimestre $(CreationDate_Genitive)]',

if( DateKey='PDF', '[Numéro trimestre de création du PDF]',

if( DateKey='Mise sous pli', '[Numéro trimestre de mise sous pli]',

if( DateKey='Impression', '[Numéro trimestre d''impression]',

if( DateKey='Annulation', '[Numéro trimestre d''annulation]',

if( DateKey='$(ExitDateKey_Name)', '[Numéro trimestre $(ExitDate_Genitive)]'))))));

/*

* Temporal granularity

*------------------------------------------------------------------------------------------------------

*/

Set Temporal_Granularity_Label =

= If(Temporal_Granularity = 0, 'jour',

If(Temporal_Granularity = 1, 'semaine',

If(Temporal_Granularity = 2, 'mois',

If(Temporal_Granularity = 3, 'trimestre',

If(Temporal_Granularity = 4, 'année')))));

Set Temporal_Granularity_Field =

= if(Temporal_Granularity=0, DateKey_Day_Label,

if(Temporal_Granularity=1, DateKey_Week_Label,

if(Temporal_Granularity=2, DateKey_Month_Label,

if(Temporal_Granularity=3, DateKey_Quarter_Label,

if(Temporal_Granularity=4, DateKey_Year_Label)))));

Set Temporal_Granularity_Number =

= if(Temporal_Granularity=0, DateKey_Value,

if(Temporal_Granularity=1, DateKey_Week_Number,

if(Temporal_Granularity=2, DateKey_Month_Number,

if(Temporal_Granularity=3, DateKey_Quarter_Number,

if(Temporal_Granularity=4, DateKey_Year_Dimension)))));

/*

* Delay and Maturity

*------------------------------------------------------------------------------------------------------

*/

Set NetWorkDay = 1; // If 1, add a field for the delay and the maturity in network day

/*

* Delay

* The delay is a difference between two dates. In some case, it could be like a duration.

*------------------------------------------------------------------------------------------------------

*/

If $(NetWorkDay) = 1 Then

Set DelayType_Label =

= if(DelayType=0, 'de traitement',

if(DelayType=1, 'ouvré'));

Set DelayType_Field =

= if(DelayType=0, '[Délai de traitement]',

if(DelayType=1, '[Délai ouvré]'));

EndIf

Set DelayGranularity_Source = 0; // 0:Day, 1:Hour, 2:Minute, 3:Seconde;

Switch $(DelayGranularity_Source)

Case 0

Set LineDelay_Granularity = Jour;

Set LineDelay_Granularity_Value = 1;

Case 1

Set LineDelay_Granularity = Heure;

Set LineDelay_Granularity_Value =

= if(LineDelay_Granularity='Heure', 1,

60);

Case 2

Set LineDelay_Granularity = Minute;

Set LineDelay_Granularity_Value =

= if(LineDelay_Granularity='Minute', 1,

if(LineDelay_Granularity='Heure', 60,

1440));

Case 3

Set LineDelay_Granularity = Seconde;

Set LineDelay_Granularity_Value =

= if(LineDelay_Granularity='Seconde', 1,

if(LineDelay_Granularity='Minute', 60,

if(LineDelay_Granularity='Heure', 3600,

86400)));

End switch

/*

* Maturity

* The maturity is a différence between two dates.

*------------------------------------------------------------------------------------------------------

*/

If $(NetWorkDay) = 1 Then

Set MaturityType_Label =

= if(MaturityType=0, '',

if(MaturityType=1, 'ouvrée'));

Set MaturityType_Field =

= if(MaturityType=0, 'Maturité',

if(MaturityType=1, '[Maturité ouvrée]'));

EndIf

/*

* Entities

*------------------------------------------------------------------------------------------------------

*/

Set Entity = 0;

Set Entity_Field =

= '[Nombre de cartes]';

Set Entity_Field_SQL = '[Nombre de cartes]';

Set Entity_Field_With_KeyDate = "if(NOT IsNull(§(Temporal_Granularity_Field)),§(Entity_Field))";

Let Entity_Field_With_KeyDate = Replace(Entity_Field_With_KeyDate, '§', '$');

// Textual

Set Entity_Value =

carte;

Set Apostrophe = 0;

Set Gender = 1;

Set Gender_Value =

= if(Gender = 1, 'e');

Set Plural = 1;

Set Plural_Value =

= if(Plural = 1, 's');

/*

* States

*------------------------------------------------------------------------------------------------------

*/

Set State_Field = ''; // Name of the state field of entity

Set State_Creation = '';

Set State_Outstanding = '';

Set State_Exit = '';

/*

* Math

*------------------------------------------------------------------------------------------------------

*/

Set Average =

"= sum(if(NOT IsNull(§(Temporal_Granularity_Field)), §(Entity_Field)))

/

count(DISTINCT §(Temporal_Granularity_Field))";

Let Average = Replace(Average, '§', '$');

/*

* Controls

*------------------------------------------------------------------------------------------------------

*/

// Key Dates

Set CreationDate_Missing = "if(IsNull(§(CreationDate_Field)), §(Entity_Field))";

Let CreationDate_Missing = Replace(CreationDate_Missing, '§', '$');

Set CreationDate_Post_Today = "if(§(CreationDate_Field) > today(0),§(Entity_Field))";

Let CreationDate_Post_Today = Replace(CreationDate_Post_Today, '§', '$');

Set ExitDate_Before_CreationDate = "if(§(ExitDate_Field) < §(CreationDate_Field), §(Entity_Field))";

Let ExitDate_Before_CreationDate = Replace(ExitDate_Before_CreationDate, '§', '$');

Set ExitDate_Post_Today = "if(§(ExitDate_Field) > today(0), §(Entity_Field))";

Let ExitDate_Post_Today = Replace(ExitDate_Post_Today, '§', '$');

Set Total_KeyDates_Controls = "sum(§(CreationDate_Missing)) + sum(§(CreationDate_Post_Today)) + sum(§(ExitDate_Before_CreationDate)) + sum(§(ExitDate_Post_Today))";

Let Total_KeyDates_Controls = Replace(Total_KeyDates_Controls, '§', '$');

// States

Let Entity_Without_State = 0;

Let ExitDate_Exist_On_State_To_Threat = 0;

Let Threated_Entity_Without_Processing_Date = 0;

Set Entity_Cancelled_Whitout_Cancel_Date = "if(len(Replace([Code annulation], ' ', ''))<>0 AND IsNull([Date d'annulation]), §(Entity_Field))";

Let Entity_Cancelled_Whitout_Cancel_Date = Replace(Entity_Cancelled_Whitout_Cancel_Date, '§', '$');

Set Entity_With_Cancel_Date_Whitout_Cancellation_Code = "if(Not IsNull([Date d'annulation]) AND len(Replace([Code annulation], ' ', ''))=0, §(Entity_Field))";

Let Entity_With_Cancel_Date_Whitout_Cancellation_Code = Replace(Entity_With_Cancel_Date_Whitout_Cancellation_Code, '§', '$');

Set Total_States_Controls = "sum(§(Entity_Cancelled_Whitout_Cancel_Date)) + sum(§(Entity_With_Cancel_Date_Whitout_Cancellation_Code))";

Let Total_States_Controls = Replace(Total_States_Controls, '§', '$');

// Delays and durations

Let Delay_Negative = 0;

Set Total_Delays_And_Durations_Controls = "sum(§(Delay_Negative))";

Let Total_Delays_And_Durations_Controls = Replace(Total_Delays_And_Durations_Controls, '§', '$');

// Others

Set Entity_Without_Required_Fields = "0";

Let Entity_Without_Required_Fields = 0;

Set Total_Others_Controls = "sum(§(Entity_Without_Required_Fields))";

Let Total_Others_Controls = Replace(Total_Others_Controls, '§', '$');

Set Total_General = "§(Total_Others_Controls) + §(Total_Delays_And_Durations_Controls) + §(Total_States_Controls) + §(Total_KeyDates_Controls)";

Let Total_General = Replace(Total_General, '§', '$');

/*

* Various

*------------------------------------------------------------------------------------------------------

*/

Set Expand_SelectionList=0;

Set Expand_ActiveSelection=0;

/*

*======================================================================================================

* Date handling

*======================================================================================================

*/

//Sub JoursFeries(dateDebut, dateFin, vlisteFeries)

Let listeFeries = Null();

Set DateDebut = "Year(Min($(CreationDate_Field)))";

Set DateFin = "Year(Max($(CreationDate_Field)))";

For Annee=2000 To 2030

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 1, 1) & chr(39); // Jour de l'an

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 5, 1) & chr(39); // Fete du travail 1er Mai

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 5, 😎 & chr(39); // Armistice 1945 8 Mai

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 7, 14) & chr(39); // Fete National 14 Juillet

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 8, 15) & chr(39); // Assomption 15 Aout

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 11, 1) & chr(39); // Toussaint 1er Novembre

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 11, 11) & chr(39); // Armistice 1918 11 Novembre

listeFeries=listeFeries & ',' & chr(39) & makedate($(Annee), 12, 25) & chr(39); // Noel

DatePaques = Date(Round(makedate($(Annee),4,day(Minute($(Annee)/38)/2+55))/7)*7-6); // Dimanche de Paques

listeFeries=listeFeries & ',' & chr(39) & DatePaques & chr(39); // Dimanche de Paques

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 1), 'DD/MM/YYYY') & chr(39); // Lundi de Paques

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 39), 'DD/MM/YYYY') & chr(39); // Jeudi de l'Ascension

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 49), 'DD/MM/YYYY') & chr(39); // Dimanche de Pentecote

listeFeries=listeFeries & ',' & chr(39) & Date((DatePaques + 50), 'DD/MM/YYYY') & chr(39); // Lundi de Pentecote

Next

Let listeFeries = Mid(listeFeries,2);

let DatePaques = Null();

Let Annee = Null();

// EndSub

If $(NetWorkDay) = 1 Then

Load

*,

Date(Date#($(CreationDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY') as $(CreationDate_Field),

Date(Date#(DATE_CREATION_PDF, 'YYYY-MM-DD'), 'DD/MM/YYYY') as [Date de création du PDF],

Date(Date#(DATE_MISE_SOUS_PLI, 'YYYY-MM-DD'), 'DD/MM/YYYY') as [Date de mise sous pli],

Date(Date#(DATE_IMPRESSION, 'YYYY-MM-DD'), 'DD/MM/YYYY') as [Date d'impression],

Date(Date#(DATE_ANNULATION, 'YYYY-MM-DD'), 'DD/MM/YYYY') as [Date d'annulation],

Date(Date#($(ExitDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY') as $(ExitDate_Field),

if (NetWorkDays(Date(Date#($(CreationDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'), Date(Date#($(ExitDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'), $(listeFeries))-1 < 0,

0,

NetWorkDays(Date(Date#($(CreationDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'), Date(Date#($(ExitDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'), $(listeFeries))-1) as [Délai ouvré f], // En jour

$(ExitDate_Source)-$(CreationDate_Source) as [Délai de traitement], // En jour

if (NetWorkDays(Date(Date#($(CreationDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'), Date(Date#($(ExitDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'))-1 < 0,

0,

NetWorkDays(Date(Date#($(CreationDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'), Date(Date#($(ExitDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'))-1) as [Délai ouvré], // En jour

Today(1)-$(CreationDate_Source) as Maturité, // En jour

if (NetWorkDays(Date(Date#($(CreationDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'), Today(1), $(listeFeries))-1 < 0,

0,

NetWorkDays(Date(Date#($(CreationDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY'), Today(1), $(listeFeries))-1) as [Maturité ouvrée]; // En jour

Else

Load

*,

Date(Date#($(CreationDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY') as $(CreationDate_Field),

Date(Date#($(ExitDate_Source), 'YYYY-MM-DD'), 'DD/MM/YYYY') as $(ExitDate_Field),

$(ExitDate_Source)-$(CreationDate_Source) as [Délai de traitement], // En jour

Today(1)-$(CreationDate_Source) as Maturité; // En jour

Endif

/*

*======================================================================================================

* Datamart Request

*======================================================================================================

*/

SQL SELECT

DateFlux,

DATE_CREATION_PDF,

DATE_MISE_SOUS_PLI,

DATE_IMPRESSION,

DATE_POSTAGE,

DATE_ANNULATION,

NUM_CLIENT as [Numéro client],

CLIENT as Client,

NUM_SOUS_GROUPE as [Id sous groupe],

SOUS_GROUPE as [Sous groupe],

DEBUT_NOM_FLUX as [Début nom flux],

CODE_ANNULATION as [Code annulation],

MOTIF_ANNULATION as [Motif annulation],

RENOUV_AUTRES as [Renouvellement autres],

NOMBRE_DE_CARTES as $(Entity_Field_SQL)

FROM "TP3G-AUTO".dbo.$(TableName_Source)

ORDER BY $(CreationDate_Source);

Rename Table $(TableName_Source) to '$(TableName_Renamed)';

/*

*======================================================================================================

* Cleaning Fields

*======================================================================================================

*/

For each DateList_Item in $(DateList_Source)

Drop field $(DateList_Item);

Next DateList_Item;

/*

*======================================================================================================

* Formating

*======================================================================================================

*/

/*

*------------------------------------------------------------------------------------------------------

* Calendrier de création

*------------------------------------------------------------------------------------------------------

*/

// Minimum and maximum dates definition

TempDate:

Load

min($(CreationDate_Field)) as minDate,

max($(CreationDate_Field)) as maxDate

Resident '$(TableName_Renamed)';

// Minimum and maximum variables definition

Let varMinDate = Num(Peek('minDate', 0, 'TempDate'));

Let varMaxDate = Num(Peek('maxDate', 0, 'TempDate'));

Drop Table TempDate;

// Generation of the continuous calendar between the variables above

TempCalendar:

Load

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

// Creating the associated calendar and dimensions

[Calendrier $(CreationDate_Genitive)]:

Load

//Dimensions

TempDate as $(CreationDate_Field),

WeekDay(TempDate) as [Jour de la semaine $(CreationDate_Genitive)],

Week(TempDate) as [Semaine $(CreationDate_Genitive)],

Month(TempDate) as [Mois $(CreationDate_Genitive)],

Ceil(Month(TempDate)/3) as [Trimestre $(CreationDate_Genitive)],

Year(TempDate) as [Année $(CreationDate_Genitive)],

// Label

WeekDay(TempDate)&' '&date(TempDate,'DD-MMM-YYYY') as [Libellé date $(CreationDate_Genitive)],

WeekYear(TempDate)&' semaine '&Week(TempDate) as [Libellé semaine $(CreationDate_Genitive)], // ISO

// Year(TempDate)&' semaine '&Week(TempDate) as [Libellé semaine de création Américain], // American

MonthName(TempDate) as [Libellé mois $(CreationDate_Genitive)],

QuarterName(TempDate) as [Libellé trimestre $(CreationDate_Genitive)],

YearName(TempDate) as [Libellé année $(CreationDate_Genitive)],

// Continuous numerical value per period

AutoNumber(WeekStart(TempDate), 'Semaine') as [Numéro semaine $(CreationDate_Genitive)], // ISO

// AutoNumber(Year(TempDate)*100 + Week(TempDateCreation), 'SemaineCréation') as [Semaine num de création Américain], // American

AutoNumber(MonthStart(TempDate), 'Mois') as [Numéro mois $(CreationDate_Genitive)],

AutoNumber(QuarterStart(TempDate), 'Trimestre') as [Numéro trimestre $(CreationDate_Genitive)]

// 'Q' & Ceil (Month(TempDate)/3) as Quarter,

// Day(TempDate) as Day,

// YeartoDate(TempDate)*-1 as CurYTDFlag,

// YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

// inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

// date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

// Week(TempDate) & '-' & WeekYear(TempDate) as WeekYear, //ISO

// Week(TempDate) & '-' & Year(TempDate) as WeekYear, //Américain

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

/*

*------------------------------------------------------------------------------------------------------

* Calendrier de traitement

*------------------------------------------------------------------------------------------------------

*/

TempDate:

Load

min($(ExitDate_Field)) as minDate,

max($(ExitDate_Field)) as maxDate

Resident '$(TableName_Renamed)';

Let varMinDate = Num(Peek('minDate', 0, 'TempDate'));

Let varMaxDate = Num(Peek('maxDate', 0, 'TempDate'));

Drop Table TempDate;

TempCalendar:

Load

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

[Calendrier $(ExitDate_Genitive)]:

Load

TempDate as $(ExitDate_Field),

WeekDay(TempDate) as [Jour de la semaine $(ExitDate_Genitive)],

Week(TempDate) as [Semaine $(ExitDate_Genitive)],

Month(TempDate) as [Mois $(ExitDate_Genitive)],

Ceil(Month(TempDate)/3) as [Trimestre $(ExitDate_Genitive)],

Year(TempDate) as [Année $(ExitDate_Genitive)],

WeekDay(TempDate)&' '&date(TempDate,'DD-MMM-YYYY') as [Libellé date $(ExitDate_Genitive)],

WeekYear(TempDate)&' semaine '&Week(TempDate) as [Libellé semaine $(ExitDate_Genitive)],

MonthName(TempDate) as [Libellé mois $(ExitDate_Genitive)],

QuarterName(TempDate) as [Libellé trimestre $(ExitDate_Genitive)],

YearName(TempDate) as [Libellé année $(ExitDate_Genitive)],

AutoNumber(WeekStart(TempDate), 'Semaine') as [Numéro semaine $(ExitDate_Genitive)],

AutoNumber(MonthStart(TempDate), 'Mois') as [Numéro mois $(ExitDate_Genitive)],

AutoNumber(QuarterStart(TempDate), 'Trimestre') as [Numéro trimestre $(ExitDate_Genitive)]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

/*

*------------------------------------------------------------------------------------------------------

* Calendrier de création du PDF

*------------------------------------------------------------------------------------------------------

*/

Set TempField_Genitive = 'de création du PDF'; // Genitive of this field

Set TempField = '[Date $(TempField_Genitive)]'; // Name of this field on QlikView

TempDate:

Load

min($(TempField)) as minDate,

max($(TempField)) as maxDate

Resident '$(TableName_Renamed)';

Let varMinDate = Num(Peek('minDate', 0, 'TempDate'));

Let varMaxDate = Num(Peek('maxDate', 0, 'TempDate'));

Drop Table TempDate;

TempCalendar:

Load

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

[Calendrier $(TempField_Genitive)]:

Load

TempDate as $(TempField),

WeekDay(TempDate) as [Jour de la semaine $(TempField_Genitive)],

Week(TempDate) as [Semaine $(TempField_Genitive)],

Month(TempDate) as [Mois $(TempField_Genitive)],

Ceil(Month(TempDate)/3) as [Trimestre $(TempField_Genitive)],

Year(TempDate) as [Année $(TempField_Genitive)],

WeekDay(TempDate)&' '&date(TempDate,'DD-MMM-YYYY') as [Libellé date $(TempField_Genitive)],

WeekYear(TempDate)&' semaine '&Week(TempDate) as [Libellé semaine $(TempField_Genitive)],

MonthName(TempDate) as [Libellé mois $(TempField_Genitive)],

QuarterName(TempDate) as [Libellé trimestre $(TempField_Genitive)],

YearName(TempDate) as [Libellé année $(TempField_Genitive)],

AutoNumber(WeekStart(TempDate), 'Semaine') as [Numéro semaine $(TempField_Genitive)],

AutoNumber(MonthStart(TempDate), 'Mois') as [Numéro mois $(TempField_Genitive)],

AutoNumber(QuarterStart(TempDate), 'Trimestre') as [Numéro trimestre $(TempField_Genitive)]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

/*

*------------------------------------------------------------------------------------------------------

* Calendrier de mise sous pli

*------------------------------------------------------------------------------------------------------

*/

Set TempField_Genitive = 'de mise sous pli'; // Genitive of this field

Set TempField = '[Date $(TempField_Genitive)]'; // Name of this field on QlikView

TempDate:

Load

min($(TempField)) as minDate,

max($(TempField)) as maxDate

Resident '$(TableName_Renamed)';

Let varMinDate = Num(Peek('minDate', 0, 'TempDate'));

Let varMaxDate = Num(Peek('maxDate', 0, 'TempDate'));

Drop Table TempDate;

TempCalendar:

Load

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

[Calendrier $(TempField_Genitive)]:

Load

TempDate as $(TempField),

WeekDay(TempDate) as [Jour de la semaine $(TempField_Genitive)],

Week(TempDate) as [Semaine $(TempField_Genitive)],

Month(TempDate) as [Mois $(TempField_Genitive)],

Ceil(Month(TempDate)/3) as [Trimestre $(TempField_Genitive)],

Year(TempDate) as [Année $(TempField_Genitive)],

WeekDay(TempDate)&' '&date(TempDate,'DD-MMM-YYYY') as [Libellé date $(TempField_Genitive)],

WeekYear(TempDate)&' semaine '&Week(TempDate) as [Libellé semaine $(TempField_Genitive)],

MonthName(TempDate) as [Libellé mois $(TempField_Genitive)],

QuarterName(TempDate) as [Libellé trimestre $(TempField_Genitive)],

YearName(TempDate) as [Libellé année $(TempField_Genitive)],

AutoNumber(WeekStart(TempDate), 'Semaine') as [Numéro semaine $(TempField_Genitive)],

AutoNumber(MonthStart(TempDate), 'Mois') as [Numéro mois $(TempField_Genitive)],

AutoNumber(QuarterStart(TempDate), 'Trimestre') as [Numéro trimestre $(TempField_Genitive)]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

/*

*------------------------------------------------------------------------------------------------------

* Calendrier d'impression

*------------------------------------------------------------------------------------------------------

*/

Set TempField_Genitive = 'd''impression'; // Genitive of this field

Set TempField = '[Date $(TempField_Genitive)]'; // Name of this field on QlikView

TempDate:

Load

min($(TempField)) as minDate,

max($(TempField)) as maxDate

Resident '$(TableName_Renamed)';

Let varMinDate = Num(Peek('minDate', 0, 'TempDate'));

Let varMaxDate = Num(Peek('maxDate', 0, 'TempDate'));

Drop Table TempDate;

TempCalendar:

Load

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

[Calendrier $(TempField_Genitive)]:

Load

TempDate as $(TempField),

WeekDay(TempDate) as [Jour de la semaine $(TempField_Genitive)],

Week(TempDate) as [Semaine $(TempField_Genitive)],

Month(TempDate) as [Mois $(TempField_Genitive)],

Ceil(Month(TempDate)/3) as [Trimestre $(TempField_Genitive)],

Year(TempDate) as [Année $(TempField_Genitive)],

WeekDay(TempDate)&' '&date(TempDate,'DD-MMM-YYYY') as [Libellé date $(TempField_Genitive)],

WeekYear(TempDate)&' semaine '&Week(TempDate) as [Libellé semaine $(TempField_Genitive)],

MonthName(TempDate) as [Libellé mois $(TempField_Genitive)],

QuarterName(TempDate) as [Libellé trimestre $(TempField_Genitive)],

YearName(TempDate) as [Libellé année $(TempField_Genitive)],

AutoNumber(WeekStart(TempDate), 'Semaine') as [Numéro semaine $(TempField_Genitive)],

AutoNumber(MonthStart(TempDate), 'Mois') as [Numéro mois $(TempField_Genitive)],

AutoNumber(QuarterStart(TempDate), 'Trimestre') as [Numéro trimestre $(TempField_Genitive)]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

/*

*------------------------------------------------------------------------------------------------------

* Calendrier d'annulation

*------------------------------------------------------------------------------------------------------

*/

Set TempField_Genitive = 'd''annulation'; // Genitive of this field

Set TempField = '[Date $(TempField_Genitive)]'; // Name of this field on QlikView

TempDate:

Load

min($(TempField)) as minDate,

max($(TempField)) as maxDate

Resident '$(TableName_Renamed)';

Let varMinDate = Num(Peek('minDate', 0, 'TempDate'));

Let varMaxDate = Num(Peek('maxDate', 0, 'TempDate'));

Drop Table TempDate;

TempCalendar:

Load

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

[Calendrier $(TempField_Genitive)]:

Load

TempDate as $(TempField),

WeekDay(TempDate) as [Jour de la semaine $(TempField_Genitive)],

Week(TempDate) as [Semaine $(TempField_Genitive)],

Month(TempDate) as [Mois $(TempField_Genitive)],

Ceil(Month(TempDate)/3) as [Trimestre $(TempField_Genitive)],

Year(TempDate) as [Année $(TempField_Genitive)],

WeekDay(TempDate)&' '&date(TempDate,'DD-MMM-YYYY') as [Libellé date $(TempField_Genitive)],

WeekYear(TempDate)&' semaine '&Week(TempDate) as [Libellé semaine $(TempField_Genitive)],

MonthName(TempDate) as [Libellé mois $(TempField_Genitive)],

QuarterName(TempDate) as [Libellé trimestre $(TempField_Genitive)],

YearName(TempDate) as [Libellé année $(TempField_Genitive)],

AutoNumber(WeekStart(TempDate), 'Semaine') as [Numéro semaine $(TempField_Genitive)],

AutoNumber(MonthStart(TempDate), 'Mois') as [Numéro mois $(TempField_Genitive)],

AutoNumber(QuarterStart(TempDate), 'Trimestre') as [Numéro trimestre $(TempField_Genitive)]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

/*

*------------------------------------------------------------------------------------------------------

* Delete unuse variables

*------------------------------------------------------------------------------------------------------

*/

Let varMinDate =;

Let varMaxDate =;

Set TableName_Renamed =;

Set DateList_Source =;

Set DateList_Item =;

Author

Un peu plus complexe que prévu

Je me pencherais dessus quand j'aurais quelques minutes.

Je pense qu'il faudrait passer par des tables temporaires.