combine different date fields in one master calender

[#1]

Hi!

I would like that my qlik application is time related.

I am using many tables from my for different sheets,each table has a datefield.

How do I combine all these dates in one calender?

So that I can select 2014 and all my reports built around different datefield use the selected year?

Kind regards,

Katleen

[MK_QSL]

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

[ecolomer]

For example:

TAR_DATA:

LOAD

  CodTask,

  Date("Task_Ini" + IterNo() - 1) as CData

  Resident TAR

  while IterNo() <= "Task_End" - "Task_Data" +1;

TAR are a FILE with:

- CodTask

- Task_Ini

- Task_End

[rwunderlich]

See also

Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/

-Rob

[#5]

Thanks already for all your help!

I have managed combining 5 dates in a commen calendar, using mr Wunderlich his cookbook.

I actually need to combine one more datefield.

The problem is that this table has no direct connection to the other tables with datefields.

How do I contrive this?

Kind regards,

Katleen

[rwunderlich]

Add a link to that other table as a new field and new rows in the link table.

For example, you could extend the example I posted to include a table of LaborHours that was not associated with any OrderId.

Concatenate (DateLink)

LOAD

  LaborDate // Fact Key

  ,LaborDate as Date // Fact Date

  ,'Labor' as DateType // Fact Type

RESIDENT Labor // from Orders

;

-Rob

http://masterssummit.com

http://robwunderlich.com

[#7]

In attatchment you get the structure that I am working with.

When trying your solution (attached emptyings_id tbl emptyings to the Datelink) I created a loop.

Can I attach emptyings through the link it has with products (product_id)?

Kind regards,

Katleen

[rwunderlich]

If you move the emptyings product_id into the DateLink table, you will have to move product_id from products to DateLink as well. That will create a synthetic key, but that should be ok in this case.

An interesting question would be the cardinality and subset ratio of card_id and product_id. Would it be possible to substitute card_id from products as the key in emptythings? ie, Is there a one-to-one relationship between products.card_id and products.product_id?

-Rob

[#9]

Is is a one-to-many relationship.

I do have another question?

When usingg your calendar from field.

Can I translate/rename the outcome fields?

For example 'InforrequestYear'  = Infos year

Kind regards,

Katleen

[rwunderlich]

Sure you can change the field names. If you are referring to the CalenderFromField SUB in the example, just change the name(s) in the SUB. You could also use a RENAME FIELD statement later in the script.

If using (Qlikview Components) Qvc.CalendarFromField(), there is an entire set of variables to control the calendar field names.

-Rob