Master Calendar Not Reading Date Properly

[Anonymous]

Hi,

Using the Master Calendar to load the date field in the form of Day, Week, Year, etc shows a dash in some records while if I create an additional dimension for that record using the Day(), Week(), or Year(), then it works. Is there something wrong in my script?

LOAD
    "OPEN_TIME" as PROpenTime,
    Date( Floor( "OPEN_TIME" ) ) as PROpenDate;
SQL SELECT * FROM databasename;

// The Master Calendar
Load
     PROpenDate,
     week( PROpenDate ) As PROpenDateWeek,
     Year( PROpenDate ) As PROpenDateYear,
     Month( PROpenDate ) As PROpenDateMonth,
     Day( PROpenDate ) As PROpenDateDay,
     YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,
     YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,
     date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,
     ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,
     Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,
     WeekDay( PROpenDate ) as PROpenDateWeekDay
Resident themaintable;

Here is a sample of the outcome:

You will notice that the second record is working properly while the first one is not.

Thank you for your response in advance.

[Anonymous]

I am trying to expand your script to apply on another field as well (PRCloseDate). This is how it looks like but I got an error:

PRMinMax:
LOAD
  min(PROpenDate) as PROpenMinDate,
  max(PROpenDate) as PROpenMaxDate,

  min(PRCloseDate) as PRCloseMinDate,
  max(PRCloseDate) as PRCloseMaxDate
Resident Problems;

let vPROpenMinDate = peek('PROpenMinDate',0,'PRMinMax');
let vPROpenMaxDate = peek('PROpenMaxDate',0,'PRMinMax');


let vPRCloseMinDate = peek('PRCloseMinDate',0,'PRMinMax');
let vPRCloseMaxDate = peek('PRCloseMaxDate',0,'PRMinMax');




PrbCalendar:
LOAD
  *,
  week( PROpenDate ) As PROpenDateWeek,
  Year( PROpenDate ) As PROpenDateYear,
  Month( PROpenDate ) As PROpenDateMonth,
  Day( PROpenDate ) As PROpenDateDay,
  YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,
  YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,
  date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,
  ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,
  Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,
  WeekDay( PROpenDate ) as PROpenDateWeekDay,

  week( PRCloseDate ) As PRCloseDateWeek,
  Year( PRCloseDate ) As PRCloseDateYear,
  Month( PRCloseDate ) As PRCloseDateMonth,
  Day( PRCloseDate ) As PRCloseDateDay,
  YeartoDate( PRCloseDate ) * -1 as PRCloseDateCurYTDFlag,
  YeartoDate( PRCloseDate , -1) * -1 as PRCloseDateLastYTDFlag,
  date( monthstart( PRCloseDate ), 'MMM-YYYY' ) as PRCloseDateMonthYear,
  ApplyMap( 'QuartersMap', month( PRCloseDate ), Null() ) as PRCloseDateQuarter,
  Week( weekstart( PRCloseDate ) ) & '-' & WeekYear( PRCloseDate ) as PRCloseDateWeekYear,
  WeekDay( PRCloseDate ) as PRCloseDateWeekDay;

LOAD date($(vPROpenMinDate)+iterno()-1) as PROpenDate
autogenerate 1
While $(vPROpenMinDate)+iterno()-1 <= $(vPROpenMaxDate);


LOAD date($(vPRCloseMinDate)+iterno()-1) as PRCloseDate
autogenerate 1
While $(vPRCloseMinDate)+iterno()-1 <= $(vPRCloseMaxDate);

Questions:

• For my knowledge: The PrbCalendar starts with "LOAD *" and doesn't end with "RESIDENT". Where does it get the information from?
• When I tried to run my edited script, I got this error:

Field not found - <PRCloseDate>

LOAD date(40974+iterno()-1) as PROpenDate

autogenerate 1

While 40974+iterno()-1 <= 42830

[Kushal_Chawda]

why do you want to create the calendar on both the date? Is there specific requirement?

[Anonymous]

Yes:

• The count of records opened within a reporting period
• The count of records closed within a reporting period
• I'm also trying to bring the trend of records in the backlog

Not related to this post: My end goal is to have a canonical calendar but I'm trying to learn and develop my skills with QlikView

I have been away from the inhand technical life for more than 10 years and this is my third month with QlikView. I love it and I did a great dashboard but as I reached to this part I feel like a bit lost 😞

[Anonymous]

Hi again.. I did more readings through the help file and I re-wrote the script to look like the following but I think I'm not applying the correct logic or my script could be totally wrong .

PRMinMax:
LOAD
  min(PROpenDate) as PROpenMinDate,
  max(PROpenDate) as PROpenMaxDate,

  min(PRCloseDate) as PRCloseMinDate,
  max(PRCloseDate) as PRCloseMaxDate
Resident Problems;

let vPROpenMinDate = peek('PROpenMinDate',0,'PRMinMax');
let vPROpenMaxDate = peek('PROpenMaxDate',0,'PRMinMax');


let vPRCloseMinDate = peek('PRCloseMinDate',0,'PRMinMax');
let vPRCloseMaxDate = peek('PRCloseMaxDate',0,'PRMinMax');




PrbCalendar:
LOAD
  *,
  week( PROpenDate ) As PROpenDateWeek,
  Year( PROpenDate ) As PROpenDateYear,
  Month( PROpenDate ) As PROpenDateMonth,
  Day( PROpenDate ) As PROpenDateDay,
  YeartoDate( PROpenDate )* -1 as PROpenDateCurYTDFlag,
  YeartoDate( PROpenDate , -1) * -1 as PROpenDateLastYTDFlag,
  date( monthstart( PROpenDate ), 'MMM-YYYY' ) as PROpenDateMonthYear,
  ApplyMap( 'QuartersMap' , month( PROpenDate ), Null() ) as PROpenDateQuarter,
  Week( weekstart( PROpenDate ) ) & '-' & WeekYear( PROpenDate ) as PROpenDateWeekYear,
  WeekDay( PROpenDate ) as PROpenDateWeekDay,

  week( PRCloseDate ) As PRCloseDateWeek,
  Year( PRCloseDate ) As PRCloseDateYear,
  Month( PRCloseDate ) As PRCloseDateMonth,
  Day( PRCloseDate ) As PRCloseDateDay,
  YeartoDate( PRCloseDate ) * -1 as PRCloseDateCurYTDFlag,
  YeartoDate( PRCloseDate , -1) * -1 as PRCloseDateLastYTDFlag,
  date( monthstart( PRCloseDate ), 'MMM-YYYY' ) as PRCloseDateMonthYear,
  ApplyMap( 'QuartersMap', month( PRCloseDate ), Null() ) as PRCloseDateQuarter,
  Week( weekstart( PRCloseDate ) ) & '-' & WeekYear( PRCloseDate ) as PRCloseDateWeekYear,
  WeekDay( PRCloseDate ) as PRCloseDateWeekDay;

LOAD
  date($(vPROpenMinDate)+iterno()-1) as PROpenDate,
  date($(vPRCloseMinDate)+iterno()-1) as PRCloseDate
autogenerate 1
While
  $(vPROpenMinDate)+iterno()-1 <= $(vPROpenMaxDate) and 
  $(vPRCloseMinDate)+iterno()-1 <= $(vPRCloseMaxDate);

[Kushal_Chawda]

see this blog

https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

[Anil_Babu_Samineni]

I may understand and read whole thread. You can create one flag for two dates and use one Date filed

Load Date1 as DateField, 'Date1' as Flag from Source1;

Load Date2 as DateField, 'Date2' as Flag from Source2;

Then create master calendar like.

Note: You don't worry about association. Master calendar can capture association. Try this and check in Data model

1. PRMinMax: 
2. LOAD 
3.   min(DateField) as PROpenMinDate, 
4.   max(DateField) as PROpenMaxDate, 
5. Resident Problems; 
6.  
7. let vPROpenMinDate = peek('PROpenMinDate',0,'PRMinMax'); 
8. let vPROpenMaxDate = peek('PROpenMaxDate',0,'PRMinMax'); 
10. PrbCalendar: 
11. LOAD 
12.   *, 
13.   week( DateField) As PROpenDateWeek, 
14.   Year( DateField) As PROpenDateYear, 
15.   Month( DateField) As PROpenDateMonth, 
16.   Day( DateField) As PROpenDateDay, 
17.   YeartoDate( DateField)* -1 as PROpenDateCurYTDFlag, 
18.   YeartoDate( DateField, -1) * -1 as PROpenDateLastYTDFlag, 
19.   date( monthstart( DateField), 'MMM-YYYY' ) as PROpenDateMonthYear, 
20.   ApplyMap( 'QuartersMap' , month( DateField), Null() ) as PROpenDateQuarter, 
21.   Week( weekstart( DateField) ) & '-' & WeekYear( DateField) as PROpenDateWeekYear, 
22.   WeekDay( DateField) as PROpenDateWeekDay, 
24. LOAD 
25.   date($(vPROpenMinDate)+iterno()-1) as PROpenDate, 
26.   date($(vPRCloseMinDate)+iterno()-1) as PRCloseDate 
27. autogenerate 1 
28. While 
29.   $(vPROpenMinDate)+iterno()-1 <= $(vPROpenMaxDate) and  
30.   $(vPRCloseMinDate)+iterno()-1 <= $(vPRCloseMaxDate);