topic Re: Master calendar and Facts table in QlikView

Master calendar and Facts table

Anonymous — Tue, 29 Sep 2015 10:38:54 GMT

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?

Re: Master calendar and Facts table

SreeniJD — Tue, 29 Sep 2015 10:41:30 GMT

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

Re: Master calendar and Facts table

swuehl — Tue, 29 Sep 2015 10:44:46 GMT

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.

Re: Master calendar and Facts table

Anonymous — Tue, 29 Sep 2015 10:48:19 GMT

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

Re: Master calendar and Facts table

Anonymous — Tue, 29 Sep 2015 10:49:34 GMT

How do i get rid of it then?

Re: Master calendar and Facts table

swuehl — Tue, 29 Sep 2015 10:50:45 GMT

Why don’t my dates work?

Re: Master calendar and Facts table

ChennaiahNallani — Tue, 29 Sep 2015 10:53:38 GMT

Hi,

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

Re: Master calendar and Facts table

qlikviewwizard — Tue, 29 Sep 2015 10:58:21 GMT

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;

Re: Master calendar and Facts table

Anonymous — Tue, 29 Sep 2015 11:23:04 GMT

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