Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
himatech
Contributor III
Contributor III

Master Calendar Not Reading Date Properly

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:

qlikview-20170404.png

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

Thank you for your response in advance.

45 Replies
himatech
Contributor III
Contributor III
Author

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?

himatech
Contributor III
Contributor III
Author

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 😞

himatech
Contributor III
Contributor III
Author

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);

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'); 
  9. PrbCalendar: 
  10. LOAD 
  11.   *, 
  12.   week( DateField) As PROpenDateWeek, 
  13.   Year( DateField) As PROpenDateYear, 
  14.   Month( DateField) As PROpenDateMonth, 
  15.   Day( DateField) As PROpenDateDay, 
  16.   YeartoDate( DateField)* -1 as PROpenDateCurYTDFlag, 
  17.   YeartoDate( DateField, -1) * -1 as PROpenDateLastYTDFlag, 
  18.   date( monthstart( DateField), 'MMM-YYYY' ) as PROpenDateMonthYear, 
  19.   ApplyMap( 'QuartersMap' , month( DateField), Null() ) as PROpenDateQuarter, 
  20.   Week( weekstart( DateField) ) & '-' & WeekYear( DateField) as PROpenDateWeekYear, 
  21.   WeekDay( DateField) as PROpenDateWeekDay, 
  22. LOAD 
  23.   date($(vPROpenMinDate)+iterno()-1) as PROpenDate, 
  24.   date($(vPRCloseMinDate)+iterno()-1) as PRCloseDate 
  25. autogenerate 1 
  26. While 
  27.   $(vPROpenMinDate)+iterno()-1 <= $(vPROpenMaxDate) and  
  28.   $(vPRCloseMinDate)+iterno()-1 <= $(vPRCloseMaxDate); 
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)