topic Re: Problem with master calendar in QlikView

Problem with master calendar

Anonymous — Mon, 25 Jul 2016 07:31:22 GMT

hi,

I have following table....

work_id name startdate enddate

1 a 11/01/2014 12/01/2014

2 b 11/01/2014 12/01/2014

2 c 12/01/2014 13/01/2014

1 c 11/01/2014 12/01/2014

3 b 13/01/2014 14/01/2014

4 c 14/01/2014 15/01/2014

like consider, i have some 1000 of records, now how do i create a master calendar for these start and end date, are there any other way to do this.?

Thanks..

Re: Problem with master calendar

PrashantSangle — Mon, 25 Jul 2016 07:36:49 GMT

Hi,

Calendar always depend on on what basis you want to do analysis your data

1: If you want to do analysis on Start Date then create calendar on Start_Date

2: If you want to do analysis on End Date then create calendar on End_Date

3: If you want to do analysis on Start Date as well as End Date then create master calendar by taking min of start_Date

and max of End date

Regards

Re: Problem with master calendar

avinashelite — Mon, 25 Jul 2016 07:38:34 GMT

Do you want to link your existing table with the master calendar ??

try like this

TEMP:

LOAD

work_id&'-'&name as Key

work_id,

name,

startdate,

enddate

from table;

Link:

LOAD Key,

startdate as date_key

resident

TEMP;

LOAD

Key,

enddate as date_key

resident

TEMP;

Master_Calendar:

LOAD Date as date_key,

......

Re: Problem with master calendar

Anonymous — Mon, 25 Jul 2016 07:50:59 GMT

if i am taking min as Startdate and max as Enddate. how would i link to the existing table.?

Re: Problem with master calendar

Anonymous — Mon, 25 Jul 2016 07:54:33 GMT

can you explain how can i link the master calendar with that existing table.?

Re: Problem with master calendar

akpofureenughwu — Mon, 25 Jul 2016 08:10:51 GMT

Hello Kishore,

You can use startdate as your date variable to generate others date fields like Quarter, Month, Week

Find the attached file....

Hope you find this helpful....

Re: Problem with master calendar

Anonymous — Mon, 25 Jul 2016 08:54:19 GMT

thank you.. i have already done with the start date.. i want to know, do i have to create a master calendar with multiple dates.!

Re: Problem with master calendar

avinashelite — Mon, 25 Jul 2016 09:00:43 GMT

In order to link multiple dates with the calendar I have created a link table where I am merging all the dates into one single date column and then finally linking the link table with the calendar table . See my last post of the sample code

Re: Problem with master calendar

Anonymous — Tue, 26 Jul 2016 03:55:10 GMT

I am not clear with your sample code.. can you explain me more on that.!

I have two tables. namely

workflow table

task table

workflow table fields.

workflow_id workflow_name start_date end_date

1 a 10/02/2014 12/02/2014

task table fields.

workflow_id task_id start_date end_date

1 1a 10/02/2014 10/02/2014

1 1b 10/02/2014 11/02/2014

Here.. each workflow_id has more than one task_id and start and end date...

I want to create a pivot table. where i have to include all these..

I want to create either master cal / canonical date.. To make selections according to d dates it can be either from workflow table or from task table

Thanks..

Thanks.

Re: Problem with master calendar

Digvijay_Singh — Tue, 26 Jul 2016 04:04:12 GMT

Can you share the KPIs you are trying to create. As they drive the decisions whether you need multiple calendars or canonical calendar or calendar based on single date, you may create canonical date from all the available dates, check HICs blog on this.

Re: Problem with master calendar

Anonymous — Tue, 26 Jul 2016 04:39:32 GMT

I have two tables. namely

workflow table

task table

workflow table fields.

workflow_id workflow_name start_date end_date

1 a 10/02/2014 12/02/2014

task table fields.

workflow_id task_id start_date end_date

1 1a 10/02/2014 10/02/2014

1 1b 10/02/2014 11/02/2014

Here.. each workflow_id has more than one task_id and start and end date...

I want to create a pivot table. where i have to include all these..

I want to create either master cal / canonical date.. To make selections according to d dates it can be either from workflow table or from task table

Thanks..

Re: Problem with master calendar

Digvijay_Singh — Tue, 26 Jul 2016 04:54:20 GMT

I think canonical date may work for you, your workflow start/end is diff from task start/end, so they shouldn't associate by keeping diff names. You can use attached sample script, here multiple calendars are also used which can be ignored.

Re: Problem with master calendar

Anonymous — Tue, 26 Jul 2016 05:12:47 GMT

Creating multiple calendars for those four date fields will help me.???

can i make a link with those workflows with tasks by creating multiple cals?

Re: Problem with master calendar

Digvijay_Singh — Tue, 26 Jul 2016 05:32:24 GMT

I think you need canonical calendar, that way you can link workflow dates and task dates, follow the sample.

Re: Problem with master calendar

Anonymous — Tue, 26 Jul 2016 06:10:59 GMT

Hi.. i followed the same and made a link between two tables.

My doubt is, can i also Create multiple master calendars for those four date fields.!! Is that possible?.. if i do that, will it link workflow dates with task dates.??

Re: Problem with master calendar

Digvijay_Singh — Tue, 26 Jul 2016 06:57:41 GMT

Indirectly all fields are connected thru IDs but for dates based KPIs u

need one holistic field representing all dates

Thanks and Regards,

Digvijay Singh

On 26 Jul 2016 11:41, "kishore pandurangan" <qcwebmaster@qlikview.com>

Re: Problem with master calendar

Anonymous — Tue, 26 Jul 2016 08:53:40 GMT

Hi.. i didn't get how the canonical date works. Can you pls explain with your sample qvw.. In which case we may use this canonical date.!

Re: Problem with master calendar

rubenmarin — Tue, 26 Jul 2016 09:44:05 GMT

Hi kishore, in case it helps, here is Canonical Date explained: Canonical Date