topic Re: How do I link my inline table to Master Calendar in QlikView

How do I link my inline table to Master Calendar

Tue, 08 Jul 2014 17:36:35 GMT

I have a Master Calendar which calculates a Month field. I want to do a simple inline table which has a Month field. But when I do It always seems to add the inline months to my Month listbox. What am I doing wrong?

Re: How do I link my inline table to Master Calendar

maxgro — Tue, 08 Jul 2014 18:19:13 GMT

if the month field in the calendar and the month field in the inline load have the same field name (Month), the listbox displays the content of the Month field, = values from calendar and inline load

or I'm missing something?

Re: How do I link my inline table to Master Calendar

Tue, 08 Jul 2014 18:50:17 GMT

Perhaps I dont really need a Master Calendar. All I am looking to do is create Year & Month list boxes so the user can fileter based upon this simple set of sample data to begin with,

LOAD * INLINE [

Event Date, Device, Users
201401,iPhone,10000
201402,iPhone,51000
201403,iPhone,52000
201404,iPhone,49000
201405,iPhone,54000
201406,iPhone,56000
201407,iPhone,23000
];

Load date#([Event Date],'YYYYMM'),
Year([Event Date]) as Year,

Month([Event Date]) as Month;

But the above doesnt seem to be working. Can you help?

Re: How do I link my inline table to Master Calendar

maxgro — Tue, 08 Jul 2014 18:59:31 GMT

try this (added a resident, in bold)

LOAD * INLINE [

Event Date, Device, Users

201401,iPhone,10000

201402,iPhone,51000

201403,iPhone,52000

201404,iPhone,49000

201405,iPhone,54000

201406,iPhone,56000

201407,iPhone,23000

];

t2:

Load

date(date#([Event Date],'YYYYMM'), 'MMM-YYYY') as MonthYear,

Year(Date#([Event Date],'YYYYMM')) as Year,

Month(Date#([Event Date],'YYYYMM')) as Month

Resident t;

or with a preceding load

Load

date(date#([Event Date],'YYYYMM'), 'MMM-YYYY') as MonthYear,

Year(Date#([Event Date],'YYYYMM')) as Year,

Month(Date#([Event Date],'YYYYMM')) as Month;

LOAD * INLINE [

Event Date, Device, Users

201401,iPhone,10000

201402,iPhone,51000

201403,iPhone,52000

201404,iPhone,49000

201405,iPhone,54000

201406,iPhone,56000

201407,iPhone,23000

];

Re: How do I link my inline table to Master Calendar

Tue, 08 Jul 2014 19:26:00 GMT

I'm close. That works. I get list boxes with Mont, Year, MonthYear. However selecting them does not filter to the appropriate value. Here is what I have:

LOAD * INLINE [
Event Date, Device, Active Users
201401,iPhone,10000
201402,iPhone,51000
201403,iPhone,52000
201404,iPhone,49000
201405,iPhone,54000
201406,iPhone,56000
201407,iPhone,23000
];

t2:
Load
date(date#([Event Date],'YYYYMM'), 'MMM-YYYY') as MonthYear,
Year(Date#([Event Date],'YYYYMM')) as Year,
Month(Date#([Event Date],'YYYYMM')) as Month,
'Q' & Ceil(Month(Date#([Event Date],'YYYYMM')) /3) as [Quarter]
Resident [Sample Table];

I tried a line chart with Month Year and Event Date as the dimension and Active Users as the Expression.

Re: How do I link my inline table to Master Calendar

maxgro — Tue, 08 Jul 2014 19:37:09 GMT

you have to associate (link) the 2 tables

in your model there is no common field (you can see it in table viewer, ctrl-t; I suppose 2 tables, no link between them); try to add a common field (bold) and see the difference in table viewer

[Sample Table]:

LOAD * INLINE [

Event Date, Device, Active Users

201401,iPhone,10000

201402,iPhone,51000

201403,iPhone,52000

201404,iPhone,49000

201405,iPhone,54000

201406,iPhone,56000

201407,iPhone,23000

];

t2:

Load

[Event Date],

date(date#([Event Date],'YYYYMM'), 'MMM-YYYY') as MonthYear,

Year(Date#([Event Date],'YYYYMM')) as Year,

Month(Date#([Event Date],'YYYYMM')) as Month,

'Q' & Ceil(Month(Date#([Event Date],'YYYYMM')) /3) as [Quarter]

Resident [Sample Table];