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

combine different date fields in one master calender

Tue, 29 Jul 2014 11:15:57 GMT

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

Re: combine different date fields in one master calender

MK_QSL — Tue, 29 Jul 2014 11:18:35 GMT

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

Re: combine different date fields in one master calender

ecolomer — Tue, 29 Jul 2014 15:17:28 GMT

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

rwunderlich — Tue, 29 Jul 2014 16:38:10 GMT

Re: combine different date fields in one master calender

Thu, 31 Jul 2014 13:36:34 GMT

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

rwunderlich — Thu, 31 Jul 2014 17:01:21 GMT

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

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

Fri, 01 Aug 2014 06:53:05 GMT

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

rwunderlich — Thu, 07 Aug 2014 15:51:09 GMT

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

Re: combine different date fields in one master calender

Mon, 11 Aug 2014 06:40:14 GMT

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

rwunderlich — Mon, 11 Aug 2014 22:29:07 GMT

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

Re: combine different date fields in one master calender

Tue, 12 Aug 2014 11:13:35 GMT

I'm using (Qlikview Components) Qvc.CalendarFromField().

Is there an explanatory manual of some kind for these variables that you say?

Kind regards,

Katleen

Re: combine different date fields in one master calender

rwunderlich — Tue, 12 Aug 2014 13:39:01 GMT

The documentation is in the QvcDocumentation.qvw file. On the Detail tab, select Qvc.Calendar and then Variables in the container. CalendarFromField uses Qvc.Calendar so the Variable doc is actually in Qvc.Calendar. I admit that could use some clarification.

You can also use the language files as documented on the Languages sheet in the Doc.

-Rob

Re: combine different date fields in one master calender

Thu, 14 Aug 2014 06:53:28 GMT

That worked, thank you!

I'am sorry but I keep having troubles

In attachment you can see the stosag_date in a list box.

I used this datefield to create a calendar from field.

But strange enough I get dates starting from 1911 in the calendar and that year doesn't exist in the stosag datefield.

How is this possible?

Re: combine different date fields in one master calender

rwunderlich — Mon, 18 Aug 2014 18:34:20 GMT

It's hard to say without seeing the data, but it looks like you may have some stosag_date values that are not being recognized as dates.

When you select 1911, what date(s) appear in the stosag_date listbox?

-Rob