Qlik Community

QlikView Documents

Documents for QlikView related information.

Linking to two or more dates

Linking to two or more dates

This tutorial presents a script pattern for assigning time dimensions to multiple fact dates in a data model. It answers the commonly asked Forum question "how do I link to two dates"?

The pattern will demonstrate how to link all fact dates to a common calendar as well as using separate calendars for each fact date.

Labels (2)
Attachments
Comments
michael_gardner
Contributor III

Hey Rob,  Thanks for the resource.  This looks like a great solution to a common problem on paper.  However, have you ever ran into situations where this caused too much ambiguity for the end user in the final application or created issues with set analysis calculations as you were developing the application?

Hi Michael,

I've not run into any problems of ambiguity or analysis problems. Before I implement the pattern, I discuss with the user what type of analysis they want. I am implementing the users specification, so there is typically no confusion as to what a "date" means. The issue has already been discussed.

Not applicable

Hello Rob,

Thanks for this tutorial, is very well explained and has been very helpful.

I would like to make you a question regarding this, I have ran into this new situation in which I have to build a chart that shows a payment schedule for a Real Estate company.

This would be a sample of the data for only one unit:

UnitPlanned DateAmountPayment DateAmount Paid
2016/12/2013    1,445.4 6/12/2013              1,445.4
2017/1/2014  30,030.9 12/1/2014            25,526.0
2017/2/2014    8,116.5 9/2/2014              8,116.5
2017/3/2014    8,114.5               8,114.5
2017/4/2014    8,109.5               8,109.5
2017/5/2014    8,112.6               8,112.6
2017/6/2014    8,114.6               8,114.6

As in your example, I have to use both date fields in my chart to make an analysis of the payment's status for each unit, but as you will see I have many rows for each unit. How should I handle this particular scenario?

Thank you in advance,

You will need to have a unique transactionId for each row. The transactionId field will be the link between the date tables(s) and the fact table.

-Rob

MCampestrini
Valued Contributor

Rob,

Thanks a lot the example. I'm now in a situation like this and it will happen a lot.

Not applicable

Any way to get the license in the attached QVW updated?  I tried loading it but get an error message saying "The license that is embedded in this document is not valid".

Not applicable

Thanks for excellent code. Just wonder trying to understand the function CalendarFromField. I am new to Qlikview but not to programming but fail to understand the sequence of execution of this function.

For example how the following load works when DateMin and DateMax is not yet defined, which defined later in the script

// Generate range of dates between min and max.

LOAD

  date(DateMin + IterNo()) as [$(_field)] // Link Field

WHILE DateMin + IterNo() <= DateMax

;

Such here and here also datefield is not defined, which is defined later

LOAD

  min(datefield)-1 as DateMin

  ,max(datefield) as DateMax

;

Also the last statement is, which is generating the datafield, so request if someone can explain  how it being working and eventually the main table is populated.

LOAD

  FieldValue('$(_field)', RecNo()) as datefield

AutoGenerate FieldValueCount('$(_field)');

Thanks. It will help me understand the function of load and script execution. Thanks.

Not applicable

Thanks. I finally got how preceding load works. Thanks for wonderful code.

Philip Lima

I'm sorry I don't have the ability to update the PE license.

-Rob

hirishv7
Honored Contributor

Thank You Very Much rwunderlich

Version history
Revision #:
1 of 1
Last update:
‎02-07-2014 02:44 PM
Updated by: