Master calendar and Facts table

[Anonymous]

I am trying to plot a graph with the month on X axis and the outage details.i.e sum of outage mins on Y axis.

Problem i am facing is that when month field created using master calendar is not being recognised

Attaching my script of fact table and master calendar in respective txt files.

I have attached screenshot of the table showcasing the issue. When I prepare a table then month name doesnt appear for the dates.Somehow the month is not getting mapped wherever there is an outage date in the table.

Can anybody please help what is going wrong here?

[SreeniJD]

It seems there is some problem with the string and date comparision, use date# function to convert the string to date and then join...

HTH

Sreeni

[swuehl · Accepted solution]

Your fact table date seems to be a timestamp with date and time. You calendar only handles dates, that's why probably the link is not working.

[Anonymous]

Can you please elaborate? I didnt understand..Attaching my base file !

[Anonymous]

How do i get rid of it then?

[swuehl]

Why don’t my dates work?

[ChennaiahNallani]

Hi,

to get month use this Exp in scrapt in master Calendar Date#(Yourdatefield,'MMM-YYYY') as Month

[qlikviewwizard]

Hi,

Open_date_IST values of Outage_details table and TempDate AS Open_date_IST of MasterCalendar may not be matching.

Use my attached script. This will work.

Change the script as shown in screen.

Outage_details:

LOAD [Incident Number],

     date(Open_date_IST) as Open_date_IST ,

     monthname(Open_date_IST) as Open_Month,

     [Outage Start Date/Time(UTC/GMT)],

     regiontime,

     Description,

     Summary,

     [Configuration Item Name],

     [Outage Cause Factor],

     [Outage Type],

     [Outage Duration (Min)],

     [Outage Ref],

     [Outage Scope],

     [Platform Failure Description],

     [Crisis Call Duration (Min)]

FROM

(ooxml, embedded labels, table is Sheet1);

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

min(Open_date_IST) as minDate, 

max(Open_date_IST) as maxDate 

Resident Outage_details; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

  TempDate AS Open_date_IST,  

  week(TempDate) As Week, 

  Year(TempDate) As Year, 

  Monthname(TempDate,0) As Month, 

  Day(TempDate) As Day, 

  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

  WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

[Anonymous]

Thanks .. Iused the floor function and it worked like a miracle..!! Thanks for pointing me to the right blog