Skip to main content
Announcements
Document boards are being consolidated, this board no longer allows NEW documents READ MORE

Linking to two or more dates

cancel
Showing results for 
Search instead for 
Did you mean: 
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Linking to two or more dates

Last Update:

Feb 7, 2014 2:44:13 PM

Updated By:

rwunderlich

Created date:

Feb 7, 2014 2:44:13 PM

Attachments

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)
Comments
Anonymous
Not applicable

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?

0 Likes
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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,

0 Likes
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

0 Likes
Marcio_Campestrini
Specialist
Specialist

Rob,

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

0 Likes
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".

0 Likes
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.

0 Likes
Not applicable

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

0 Likes
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Philip Lima

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

-Rob

0 Likes
HirisH_V7
Master
Master

Thank You Very Much rwunderlich

0 Likes
Version history
Last update:
‎2014-02-07 02:44 PM
Updated by: