Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Fact Table With Several Date Fields

Hello Friends,

I'm making a report for an assurance company. This report have just one fact table with differents dates related to a Principal Key that represent a unique code for the registered accident. The differents metrics have to be represented on a timeline chart using this dates fields. Here is an example of the fact table.

PrincipalCodeDate1Date2Date3Date4
A01/01/201501/01/201601/02/201620/02/2016
B01/10/201501/12/201501/01/201620/01/2016
C01/03/201401/06/201501/07/201530/07/2015
D01/04/201201/10/201531/10/201530/11/2015

I want to relate this dates to a principal calendar, I can't use Link Tables because I have only one fact table, I'm trying to solve this using differents master calendar, its a good idea?

Thanks for your help

Best Regards

5 Replies
swuehl
MVP
MVP

Try to use a CROSSTABLE LOAD prefix

The Crosstable Load

This will create a single column containing all your dates and another column with the labels ('Date1','Date2'..)

You can link the single date column easily to a master calendar

The Master Calendar

Anonymous
Not applicable
Author

First do the transformation using Cross Load, like this?

The Crosstable Load

And then see this to create a Master Calender?

Canonical Date

Anonymous
Not applicable
Author

By using the rangesum to solve the problem or by using the cross table

Anonymous
Not applicable
Author

Hi Swuehl,

Thanks for your answer,I use crosstable and "yes" it works fine if I want to select differents kind of dates. But what happend if I need to show the information like this on a chart.

 

Date201/01/201501/02/201501/03/201501/04/201501/05/201501/06/201501/07/201501/08/2015
Date1
01/01/201510001000100010001000100010001000
01/02/20151000100010001000100010001000
01/03/2015100010001000100010001000
01/04/201510001000100010001000
01/05/20151000100010001000
01/06/2015100010001000
01/07/201510001000
01/08/201510001000
swuehl
MVP
MVP

Have you tried a pivot table chart and two dimensions, like

=If(DateType = 'Date1', DateField)

=If(DateType = 'Date2', DateField)

with DateType and DateField being the two fields created by the CROSSTABLE LOAD.