Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

combine different date fields in one master calender

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

13 Replies
MVP
MVP

Re: combine different date fields in one master calender

ecolomer
Honored Contributor II

Re: combine different date fields in one master calender

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

Re: combine different date fields in one master calender

See also

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

-Rob

Not applicable

Re: combine different date fields in one master calender

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

Re: Re: combine different date fields in one master calender

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

;

2014-07-31_9-59-29.png

-Rob

http://masterssummit.com

http://robwunderlich.com

Not applicable

Re: Re: Re: combine different date fields in one master calender

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

Re: combine different date fields in one master calender

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

Not applicable

Re: combine different date fields in one master calender

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

Re: combine different date fields in one master calender

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

Community Browser