topic Re: Fact Table With Several Date Fields in QlikView

Fact Table With Several Date Fields

Anonymous — Wed, 24 Feb 2016 22:21:38 GMT

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.

PrincipalCode	Date1	Date2	Date3	Date4
A	01/01/2015	01/01/2016	01/02/2016	20/02/2016
B	01/10/2015	01/12/2015	01/01/2016	20/01/2016
C	01/03/2014	01/06/2015	01/07/2015	30/07/2015
D	01/04/2012	01/10/2015	31/10/2015	30/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

Re: Fact Table With Several Date Fields

swuehl — Wed, 24 Feb 2016 23:18:43 GMT

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

Re: Fact Table With Several Date Fields

Anonymous — Thu, 25 Feb 2016 06:10:35 GMT

First do the transformation using Cross Load, like this?

The Crosstable Load

And then see this to create a Master Calender?

Canonical Date

Re: Fact Table With Several Date Fields

Anonymous — Thu, 25 Feb 2016 06:14:01 GMT

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

Re: Fact Table With Several Date Fields

Anonymous — Thu, 25 Feb 2016 17:07:14 GMT

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.

Date2	01/01/2015	01/02/2015	01/03/2015	01/04/2015	01/05/2015	01/06/2015	01/07/2015	01/08/2015
Date1
01/01/2015	1000	1000	1000	1000	1000	1000	1000	1000
01/02/2015		1000	1000	1000	1000	1000	1000	1000
01/03/2015			1000	1000	1000	1000	1000	1000
01/04/2015				1000	1000	1000	1000	1000
01/05/2015					1000	1000	1000	1000
01/06/2015						1000	1000	1000
01/07/2015							1000	1000
01/08/2015							1000	1000

Re: Fact Table With Several Date Fields

swuehl — Thu, 25 Feb 2016 17:16:12 GMT

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.