Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
how can I create a master calendar date from 2 dates : startdate & enddate in one table ?
Thanks for your help.
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
Can you share the QVD with us?
See if this can help.
From Rob Wunderlinch
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
See Attachment the QVD file.
thanks
See HC's blog post about this sort of issue: Canonical Date
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
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
Thanks you for your help.