Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

birkirbjorns
New Contributor III

Multiple dates in one facttable(for simplicity to begin with)

Hi,

I'm relatively new to Qlikview but I am experienced in creating Datawarehouses and OLAP solutions. So I guess that my mind is in someway stuck in that kind of datamodeling. I the more I play with Qlikview the more I see that datamodeling is done in a slightly different way then traditional BI solutions are modeled.

So I have  one facttable in this demo that has 3 different dates. So I created three different timedimensions for each Timekey, based on this article http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date.

But now I want to create one TimeDimension that will connect to the facttable and remove the other connections, A link table I guess. But for the life of me I cannot get this done.  So how would I create this linktable based on my data? I will also add new facttables to the model and that will create more timedimenions.

So any hints are appreciated. Here is a snapshot of the model as well as the loadscript.

Regards

Birkir, and thank you for a great communitiy.

P.S. The facttable is a daily snapshot table.

[ProductSubscribersFact]:

LOAD CustomerDimDW_Id,

     ProductDimDW_Id,

     age,

     LoadDateDimDW_Id as LoadDateTimeDimDW_Id,

     NetworkDimDW_Id,

     GildirFraTimeDimDW_Id,

     Fjoldi,

     SubscriptionTypeDimDW_Id,

     RetthafiCustomerDimDW_Id,

     GildirTilTimeDimDW_Id

FROM

$(vcQvdPath)ProductSubscribersFact.qvd

(qvd);

[LoadTimeDim]:

LOAD //TimeDimPK as LoadTimeDimPK,

     LoadDateTimeDimDW_Id,

     TheDate as LoadTheDate,

     YearName as LoadYearName,

     MonthNameLong as LoadMonthNameLong

  

FROM

$(vcQvdPath)vw_TimeDim.qvd

(qvd);

[GildirFraTimeDim]:

LOAD //TimeDimPK as GildirFraTimeDimPK,

     LoadDateTimeDimDW_Id as GildirFraTimeDimDW_Id,

     TheDate as GildirFraTheDate,

     YearName as GildirFraYearName,

     MonthNameLong as GildirFraMonthNameLong

FROM

$(vcQvdPath)vw_TimeDim.qvd

(qvd);

MultiDatesInOneFactTable.PNG.png

[GildirTilTimeDim]:

LOAD //TimeDimPK as GildirFraTimeDimPK,

     LoadDateTimeDimDW_Id as GildirTilTimeDimDW_Id,

     TheDate as GildirTilTheDate,

     YearName as GildirTilYearName,

     MonthNameLong as GildirTilMonthNameLong

FROM

$(vcQvdPath)vw_TimeDim.qvd

(qvd);

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Multiple dates in one facttable(for simplicity to begin with)

Birkir,

you essentially want to create the bridge table from Henric's blog post, right?

What have you tried so far?

Here is another thread with a very similar topic and a link to a tutorial created by Rob Wunderlich:

Tutorial - Using Common Date Dimensions and Shared Calendars

View solution in original post

3 Replies
MVP
MVP

Re: Multiple dates in one facttable(for simplicity to begin with)

Birkir,

you essentially want to create the bridge table from Henric's blog post, right?

What have you tried so far?

Here is another thread with a very similar topic and a link to a tutorial created by Rob Wunderlich:

Tutorial - Using Common Date Dimensions and Shared Calendars

View solution in original post

birkirbjorns
New Contributor III

Re: Multiple dates in one facttable(for simplicity to begin with)

Hi Swuehl,

Thanks for your reply. And yes that is just what I am trying to do create a bridge table like Henric's explained. But somehow I am lost in this and I am struggling to figure out or understand what should be included in my bridge table.

I looked at the tutorial and I think it might have solved it. Thank you for your help.

Next is try to add a fact table and keep the data model correct.

Thanks again.

birkirbjorns
New Contributor III

Re: Multiple dates in one facttable(for simplicity to begin with)

Hi

Just one more comment, I have created a bridge or a link table as the tutorial showed. But the fact is loaded once as a facttable and then 1x per datetype in the link, is that correct? Is it just my old background of datamodeling troubling me is this acceptable behaviour in the qlikview world?

This is my script

DateLink:
LOAD
ProductSubscription_Id 
,LoadDateTimeDimDW_Id as TimeDimPK 
,'LoadDate' as DateType 
RESIDENT ProductSubscribersFact
;
LOAD
ProductSubscription_Id 
,GildirFraTimeDimDW_Id as TimeDimPK 
,'GildirFra' as DateType 
RESIDENT ProductSubscribersFact
;
LOAD
ProductSubscription_Id 
,GildirTilTimeDimDW_Id as TimeDimPK 
,'GildirTil' as DateType 
RESIDENT ProductSubscribersFact
;