How to create master calendarDate from 2 dates

[suzel404]

Hi all,

how can I create a master calendar date from 2 dates : startdate & enddate in one table ?

Thanks for your help.

[sunny_talwar]

Can you share the QVD with us?

[satishkurra]

See if this can help.

From Rob Wunderlinch

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

TMP:
LOAD RecNo() as ID,
    CODE,
    START_DATE,
    END_DATE
FROM [QlikCommunity_Thread_206159.qvd] (qvd);

tabDateLink:
CrossTable(DateType,Date)
LOAD ID,
    Date(START_DATE) as Start,
    Date(END_DATE) as End
Resident TMP;

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Date) as MinDate, 
    Max(Date) as MaxDate 
Resident tabDateLink;

hope this helps

regards

Marco

[suzel404]

See Attachment the QVD file.

thanks

[jonathandienst]

See HC's blog post about this sort of issue: Canonical Date

[suzel404]

I initiate this code :

But lines are maybe demultiplicated.

LOAD 

  CODE,

  YEAR(START_DATE) AS YEAR,

  MONTH(START_DATE) AS MONTH,

  NUM(START_DATE) AS START_DATE,

  NUM(END_DATE) AS END_DATE

FROM (qvd);

Number CODE in Jan 2016 : 525

Number CODE in Fev. 2016 : 493

When I create a master calendar number CODE of janv. 2016 and Fev 2016 are differents.

LOAD

  CODE,

  START_DATE,

  END_DATE,

  CALENDAR_DATE,

  YEAR(CALENDAR_DATE) AS CALYEAR,

  NUM(MONTH(CALENDAR_DATE)) AS CALMONTHNUM,

  MONTH(CALENDAR_DATE) AS CALMONTHNAME,

  WEEK(CALENDAR_DATE) AS CALWEEKNUM

  ;

LOAD

  *,

  NUM(START_DATE + ITERNO()-1)  AS CALENDAR_DATE

WHILE START_DATE + ITERNO() - 1 <  END_DATE

  ;

LOAD

  CODE,

  START_DATE,

// END_DATE

  IF(END_DATE = START_DATE , END_DATE +1, END_DATE) AS END_DATE

  ;

LOAD

  CODE,

  NUM(START_DATE) AS START_DATE,

  NUM(END_DATE) AS END_DATE

FROM (qvd);

Thanks you for your help.