topic Re: How to link master calendar dates to your dates in data in QlikView

How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 11:04:10 GMT

My requirement is that i have to indicate if an event had occurred on day. It is like a boolean, either i have data for that day or i do not have data for that day. For this i had to create and use master calendar so that all the dates for a given month are there (irrespective of whether i have the data for that day or not). If I have data for that day I have to mark that data is available (any kind of visualization like bar or line chart is okay) and if i don't have data i could leave it blank by not marking anything.

What I tried:

1. On X-Axis i had dates from my data (not master calendar) and selected Continuous axis which put all the dates (though i don't have data for a day). But the problem is that scroll bar does not appear and x-axis scale is automatically set and i have no control over it.

2. I used master calendar, which puts all the dates on X-axis but i have a problem in linking my data to master calendar dates (both are two different tables and have no link with each other).

Thanks for your support in advance.

Re: How to link master calendar dates to your dates in data

jpenuliar — Tue, 24 Mar 2015 11:10:55 GMT

You will have to rename your Master Calendar Key Field same as the date field in your fact to create a link

e.g.

Tempdate as Field,

Re: How to link master calendar dates to your dates in data

Gabriel — Tue, 24 Mar 2015 11:11:08 GMT

Hi,

It might help to post a sample App.

Also I would imagine there should be something like EVENT_DATE from the Fact table. If there is I will link EVENT_DATE with master calendar.

Re: How to link master calendar dates to your dates in data

MK_QSL — Tue, 24 Mar 2015 11:13:16 GMT

Can you provide little sample data to work on your example?

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 11:34:51 GMT

Hi all

Thanks for your quick responses. Attached is the sample app. There are two tables in it Facts and Master Calendar. Notice that in Facts_Dates list the dates 8th, 9th and 16th are missing. But Test_Facts_Dates list all the dates are present. Now the x-axis has Test_Facts_Dates, but i have to indicate that on 8th, 9th and 16th there is no data (as there are no entries Facts_Dates).

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 11:37:13 GMT

Thanks for your reply Jonathan. This is a problem for me because my original data gets mixed up with dates i created and i can never separate them.

Re: How to link master calendar dates to your dates in data

jpenuliar — Tue, 24 Mar 2015 11:38:36 GMT

Change the code:

Date(TempDate,'YYYY-MM-DD') AS [Test_Facts_Dates],

Date(TempDate,'YYYY-MM-DD') AS [Facts_Dates],

Re: How to link master calendar dates to your dates in data

jpenuliar — Tue, 24 Mar 2015 11:39:21 GMT

Your Fields have to be the same Name to establish a link

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 11:41:58 GMT

rename Test_Fact_Dates to Fact_Dates and try to plot Fact_Dates on X-axis of your graph.

amay

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 11:42:55 GMT

Thank you Jonathan for your suggestion. But, this might be a problem for me because my original data (Facts_Dates) gets mixed up with dates i created (Test_Facts_Dates) and i can never separate them. Also I have many graphs using Facts_Dates as expressions (for example - Count([Facts_Dates]))) and dimensions. They might not work as expected if i do this.

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 11:45:02 GMT

Thanks for your suggestion Amay. Please see my concern in reply to Jonathan's post. Do you think I can have the field names same and still get around the concern I mentioned.

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 11:48:41 GMT

You can try this expression

Count(if(Facts_Dates=Test_Facts_Dates,Facts_Dates))

and in presentation tab don't suppress zero values.

amay

Re: How to link master calendar dates to your dates in data

MK_QSL — Tue, 24 Mar 2015 11:49:33 GMT

Facts:

LOAD Date(Date#(@1,'YYYY-MM-DD')) as Facts_Dates

FROM

[..\2. Manish\Community\157310.xls]

(biff, no labels, table is Sheet1$);

QuartersMap:

MAPPING LOAD

rowno() as Month,

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

AUTOGENERATE (12);

Temp:

Load

min([Facts_Dates]) as minDate,

max([Facts_Dates]) as maxDate

Resident Facts;

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

Date(TempDate,'YYYY-MM-DD') AS Facts_Dates,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

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: How to link master calendar dates to your dates in data

jpenuliar — Tue, 24 Mar 2015 11:50:16 GMT

Think of the Master Calendar as a Dimension Table where a specific Date is linked only to the matching Date in the facts Tables, similar to vlookup. If I have a specific Date from Facts table (05/03/2015) I can return the Week Number equivalent from the Master calendar . I do not believe you mixed the dates since you did not join nor concatenate the tables,

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 11:54:28 GMT

Hi Sandeep,

You change your date into number then link with master .

like num(Date) as Date .

same in your transaction table.

the result will be perfect.

Regards

Vimlesh

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 12:02:09 GMT

Having a field name same in two tables, Qlikview automatically joins the tables. Result is attached in reply to this original post.

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 12:02:59 GMT

Hi All

I changed the field name in Master Calendar to Facts_Dates. The result is attached. Notice that though i don't have Facts_Dates on 8th,9th and 16th from original data, it shows bars on those dates as well, misleading that data is available on those dates.

Re: How to link master calendar dates to your dates in data

jpenuliar — Tue, 24 Mar 2015 12:04:16 GMT

I see your point, try the below:

Facts:

LOAD
1 as Counter,
@1 as Facts_Dates
FROM

(biff, no labels);

Instead of Counting Fact_Dates, Count(Counter)

Re: How to link master calendar dates to your dates in data

sunny_talwar — Tue, 24 Mar 2015 12:07:05 GMT

Would you be able to post the script for the table which contains the your date column and the script for your master calendar here. Not really sure what got mixed up?

Best,

Sunny

Re: How to link master calendar dates to your dates in data

Tue, 24 Mar 2015 13:04:46 GMT

PFA...

HTH

Amay