topic Re: Master Calendar Not Reading Dat in QlikView

Master Calendar Not Reading Date Properly

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Master Calendar Not Reading Dat

ahaahaaha — Tue, 04 Apr 2017 06:56:04 GMT

Hi,

May be in 12 line

Day(Date#(PROpenDate, 'DD/MM/YYYY')) As PROpenDateDay,

Re: Master Calendar Not Reading Dat

Anonymous — Tue, 04 Apr 2017 07:01:42 GMT

Nope.. It's giving me the same results .

Re: Master Calendar Not Reading Dat

vikasmahajan — Tue, 04 Apr 2017 07:15:24 GMT

Try this

Day(Date (Num(Evaluate(DATE) )) ) As PROpenDateDay,

Vikas

Re: Master Calendar Not Reading Dat

Anonymous — Tue, 04 Apr 2017 07:27:41 GMT

I think you meant to say:

!Day( Date( Num( Evaluate( PROpenDateDay ) ) ) ) As PROpenDateDay,

Still no luck.. in fact it gave me wrong results even for the correct record, the PROpenDateDay value showed 30!

Re: Master Calendar Not Reading Dat

Anonymous — Tue, 04 Apr 2017 07:30:34 GMT

Sorry here is the correct statement but still no luck:

Day( Date( Num( Evaluate( PROpenDate ) ) ) ) As PROpenDateDay,

Re: Master Calendar Not Reading Dat

Kushal_Chawda — Tue, 04 Apr 2017 07:33:18 GMT

try this

Day(trim(PROpenDate)) As PROpenDateDay,

Re: Master Calendar Not Reading Dat

Anonymous — Tue, 04 Apr 2017 07:40:44 GMT

Not working.. it's giving me the same outcome from my original post.

Re: Master Calendar Not Reading Dat

ashok1203 — Tue, 04 Apr 2017 07:46:13 GMT

Can you try this

Day( Num#(PROpenDate) ) As PROpenDateDay,

Re: Master Calendar Not Reading Dat

Kushal_Chawda — Tue, 04 Apr 2017 07:52:55 GMT

try creating it in below load statement and then call it in calendar

LOAD

"OPEN_TIME" as PROpenTime,

Date( Floor( "OPEN_TIME" ) ) as PROpenDate,

Day( Floor( "OPEN_TIME" ) ) as PROpenDateDay;

SQL SELECT * FROM databasename;

Re: Master Calendar Not Reading Dat

Anonymous — Tue, 04 Apr 2017 07:53:51 GMT

Not working and even the correct record stopped working as well.

Re: Master Calendar Not Reading Dat

ashok1203 — Tue, 04 Apr 2017 08:01:13 GMT

Day( date(Num#(PROpenDate),'DD/MM/YYYY' )) As PROpenDateDay,

Re: Master Calendar Not Reading Dat

Anonymous — Tue, 04 Apr 2017 08:02:40 GMT

It's working!!

I tried the following too but it didn't work. In the calendar, I added:

Day( Floor( PROpenDate ) ) as PROpenDateDay

Why putting in the original load statement works while the second one it doesn't?

Re: Master Calendar Not Reading Dat

Anonymous — Tue, 04 Apr 2017 08:06:05 GMT

Same result: both records are not working.

Re: Master Calendar Not Reading Dat

jonathandienst — Tue, 04 Apr 2017 08:46:42 GMT

I assume that PROpenDate is the date field in the fact table too, so it is the key linking the fact to the calendar. It looks likely that there are value of PROpenDate in the fact table that do not exist in the calendar (the first record).

Does "OPEN_TIME" cover all possible values of PROpenDate in the fact? I suspect not.

You can test this easily in the table viewer. Find and hover over PROpenDate in the calendar table and check that it is a key field (linked to other tables), and that the subset ratio is 100% (so all possible value exist in the calendar table).

Re: Master Calendar Not Reading Dat

Anonymous — Tue, 04 Apr 2017 09:22:18 GMT

Yes PROpenDate is the date field in the fact table and it is the key linking the fact to the calendar.

I tried to follow your advice and here is what I found:

I observed the following:

The "OPEN_TIME" has null values against the PROpenDate although both of them are getting the data from the same field name.
When I selected the value of 10/07/2014 in the PROpenDate, it showed me to records in the bottom table and one record in the upper one. Strange!

Re: Master Calendar Not Reading Dat

Kushal_Chawda — Tue, 04 Apr 2017 10:11:04 GMT

I am not sure, what could be the exact reason, but instead directly taking resident try to create calendar based on min and max date

MinMax:

LOAD min(PROpenDate) as MinDate,

max(PROpenDate) as MaxDate

Resident themaintable;

let vMinDate = peek('MinDate',0,'MinMax');

let vMaxDate = peek('MaxDate',0,'MinMax');

Calendar:

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;

LOAD date($(vMinDate)+iterno()-1) as PROpenDate

autogenerate 1

While $(vMinDate)+iterno()-1 <= $(vMaxDate);

Re: Master Calendar Not Reading Dat

jonathandienst — Tue, 04 Apr 2017 10:44:07 GMT

I would not base my calendar on OPEN_TIME. Rather use the suggestion above by kush141087

Re: Master Calendar Not Reading Dat

Anonymous — Wed, 05 Apr 2017 06:15:46 GMT

Hi kush141087‌,

Thank you for your help!

I copied your code into my script. The result doesn't seem to be correct:

Re: Master Calendar Not Reading Dat

Kushal_Chawda — Wed, 05 Apr 2017 07:04:48 GMT

can you please share sample, so that it will be easy to catch the issue?