topic Re: Script calendrier in QlikView

Script calendrier

Thu, 24 Oct 2013 14:18:41 GMT

Bonjour,

Merci de m'aider a corrigé ce script:

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:

LOAD

TempDate AS Datefac1,

TempDate1 AS Datetrai1,

TempDate2 AS DateHisto1

Day(TempDate) AS CalendarDay,

WeekDay(TempDate) AS CalendarWeekDay,

Week(TempDate) AS CalendarWeek,

Month(TempDate) AS Mois,

Year(TempDate) AS Année,

Day(TempDate1) AS CalendarDay,

WeekDay(TempDate1) AS CalendarWeekDay,

Week(TempDate1) AS CalendarWeek,

Month(TempDate1) AS Mois,

Year(TempDate1) AS Année,

Day(TempDate2) AS CalendarDay,

WeekDay(TempDate2) AS CalendarWeekDay,

Week(TempDate2) AS CalendarWeek,

Month(TempDate2) AS Mois,

Year(TempDate2) AS Année,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

'Q' & Ceil(Month(TempDate1)/3) AS CalendarQuarter,

'Q' & Ceil(Month(TempDate2)/3) AS CalendarQuarter,

WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,

WeekDay(TempDate1) & '-' & Year(TempDate1) AS CalendarWeekAndYear,

WeekDay(TempDate2) & '-' & Year(TempDate2) AS CalendarWeekAndYear,

Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,

Month(TempDate1) & '-' & Year(TempDate1) AS CalendarMonthAndYear,

Month(TempDate2) & '-' & Year(TempDate2) AS CalendarMonthAndYea,

RESIDENT TempCalendar ORDER BY TempDate,TempDate1,TempDate2, ASC;

DROP TABLE TempCalendar;

LET vDateMin = Num(MakeDate(2013,1,1));

LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));

LET vDateToday = Num(Today());

Script chargement table:

load

Datefac,

if(Right([Datefac1],2) <> 00, Date(Date#([Datefac],'YYYYMMDD'),'DD/MM/YYYY')) as Datefac1,

Datetrai,

if(Right([Datetrai],2) <> 00, Date(Date#([Datetrai],'YYYYMMDD'),'DD/MM/YYYY')) as Datetrai1,

Datehisto,

if(Right([Datehisto],2) <> 00, Date(Date#([Datehisto],'YYYYMMDD'),'DD/MM/YYYY')) as Datehisto1,

SELECT*

FROM tablette;

En attente de vous lire.

Merci.

Re: Script calendrier

Gysbert_Wassenaar — Thu, 24 Oct 2013 14:55:14 GMT

This should at least run without errors. It doesn't make any sense to duplicate all these fields though.

LET vDateMin = Num(MakeDate(2013,1,1));
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));
LET vDateToday = Num(Today());

TempCalendar:
LOAD DateNumber, TempDate, TempDate as TempDate1, TempDate as TempDate2;
LOAD
  $(vDateMin) + RowNo() - 1 AS DateNumber,
  Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

MasterCalendar:
LOAD
    TempDate AS Datefac1,
    TempDate1 AS Datetrai1,
    TempDate2 AS DateHisto1
    Day(TempDate) AS CalendarDay,
    WeekDay(TempDate) AS CalendarWeekDay,
    Week(TempDate) AS CalendarWeek,
    Month(TempDate) AS Mois,
    Year(TempDate) AS Année,
    Day(TempDate1) AS CalendarDay1,
    WeekDay(TempDate1) AS CalendarWeekDay1,
    Week(TempDate1) AS CalendarWeek1,
    Month(TempDate1) AS Mois1,
    Year(TempDate1) AS Année1,
    Day(TempDate2) AS CalendarDay2,
    WeekDay(TempDate2) AS CalendarWeekDay2,
    Week(TempDate2) AS CalendarWeek2,
    Month(TempDate2) AS Mois2,
    Year(TempDate2) AS Année2,
    'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,
    'Q' & Ceil(Month(TempDate1)/3) AS CalendarQuarter1,
    'Q' & Ceil(Month(TempDate2)/3) AS CalendarQuarter2,
    WeekDay(TempDate) & '-' & Year(TempDate) AS CalendarWeekAndYear,
    WeekDay(TempDate1) & '-' & Year(TempDate1) AS CalendarWeekAndYear1,
    WeekDay(TempDate2) & '-' & Year(TempDate2) AS CalendarWeekAndYear2,
    Month(TempDate) & '-' & Year(TempDate) AS CalendarMonthAndYear,
    Month(TempDate1) & '-' & Year(TempDate1) AS CalendarMonthAndYear1,
    Month(TempDate2) & '-' & Year(TempDate2) AS CalendarMonthAndYear2
RESIDENT TempCalendar ;

DROP TABLE TempCalendar;

You should probably create three calendars. One for Datefac1, one for Datetrai1 and one for DateHisto1. See this discussion: Tutorial - Using Common Date Dimensions and Shared Calendars

Re: Script calendrier

Thu, 24 Oct 2013 16:01:23 GMT

G Wassenaar,

Après la copie de votre script vers le mien,j'ai un message d'erreur :

Erreur de syntaxe, FROM manquant ou mal placé :

MasterCalendar:

Si je comprends ,je dois crée trois tempcalendar pour chacun des dates?

Merci de ton aide.