Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ce_gutierrez
Contributor III
Contributor III

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

balrajahlawat
Champion
Champion

First do the transformation using Cross Load, like this?

The Crosstable Load

And then see this to create a Master Calender?

Canonical Date

vsudhakar
Creator III
Creator III

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

ce_gutierrez
Contributor III
Contributor III
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.