topic Re: How to create master calendarDate from 2 dates in QlikView

How to create master calendarDate from 2 dates

suzel404 — Thu, 18 Feb 2016 20:59:38 GMT

Hi all,

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

Thanks for your help.

Re: How to create master calendarDate from 2 dates

sunny_talwar — Thu, 18 Feb 2016 21:00:55 GMT

Can you share the QVD with us?

Re: How to create master calendarDate from 2 dates

satishkurra — Thu, 18 Feb 2016 21:02:36 GMT

See if this can help.

From Rob Wunderlinch

Re: How to create master calendarDate from 2 dates

MarcoWedel — Thu, 18 Feb 2016 22:20:48 GMT

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

Re: How to create master calendarDate from 2 dates

suzel404 — Fri, 19 Feb 2016 05:02:49 GMT

See Attachment the QVD file.

thanks

Re: How to create master calendarDate from 2 dates

jonathandienst — Fri, 19 Feb 2016 05:30:50 GMT

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

Re: How to create master calendarDate from 2 dates

suzel404 — Fri, 19 Feb 2016 05:35:50 GMT

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.