Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

valpassos
New Contributor III

Not another mixed granularity fact tables...

Hi community   I know this type of question sounds repetitive but I'm really newbie at Qlik and I just need a minimal guidance, because I've read so much techniques and best practices that I'm kind of lost now...

I have the following hierarchies in different files:

Table1:

Date

GrandFather

Father

Child

GrandChild

Fact1

...

Table2:

Date

Father

Child

Fact2

Fact3

...

Table3:

Date

GrandFather

RandomPerson1

Father

RandomPerson2,

RandomPerson3,

Fact4

...

I want to associate the three tables, so I created a composite key (AutoNumber) between Date, Father and Child to associate the first two tables, and it worked fine. In a fact table I have put all the different facts and this key that links the two tables, and dropped the fields from the respective tables. I also have a Master Calendar with the Date key field.

But now I'm tring to do the same thing between table1 and table3, but somehow the Date, GrandFather and Father composite key is not the same in the two tables, so it doesn't link them...

What are the options in terms of modeling this type of situationn? Generic Keys and Link Tables?

Please offer some advice, however trivial it may sound.


Lisa

1 Solution

Accepted Solutions
ogster1974
Honored Contributor II

Re: Not another mixed granularity fact tables...

Concatenate the three tables together.

Source

GrandFather

Father

Child

GrandChild

RandomPerson1

RandomPerson2

RandomPerson3

Date

Fact1

Fact2

Fact3

Fact4

Regards

Andy

12 Replies
adityaakshaya
Contributor III

Re: Not another mixed granularity fact tables...

Hi Lisa,

Can you share your data model here. Did you try using concatenating three facts together.

Regards,

Akshaya

ogster1974
Honored Contributor II

Re: Not another mixed granularity fact tables...

Concatenate the three tables together.

Source

GrandFather

Father

Child

GrandChild

RandomPerson1

RandomPerson2

RandomPerson3

Date

Fact1

Fact2

Fact3

Fact4

Regards

Andy

vkish16161
Contributor III

Re: Not another mixed granularity fact tables...

Looks like you have to use

Hierarchy and Hierachy belongs to. This creates a linkage between every Parent and Child.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

valpassos
New Contributor III

Re: Not another mixed granularity fact tables...

Hi Akshaya,

To concatenate don't I need to have the two tables at the same granularity? My Table3 has a lot of other fields that aren't shared by Table1. Wouldn't that be a problem?

I wish I could share some sort of mockup of my model but I'm finding it hard to abstract the terminology used.

Thanks,

Lisa

adityaakshaya
Contributor III

Re: Not another mixed granularity fact tables...

Hi Lisa,

I don't see any such challenges by concatenating data.

Regards,

Akshaya

valpassos
New Contributor III

Re: Not another mixed granularity fact tables...

Hi Andy

Thanks for your help.

So, what you are saying is I could have a single fact table with all the filtering dimensions/fields collapsed into it? Even the Calendar?

It seems an odd solution. Why wouldn't that be always the preferred way to concatenate tables that share some fields? Why the need, so, for Generic Keys and whatelse?

Thanks,

Lisa

ogster1974
Honored Contributor II

Re: Not another mixed granularity fact tables...

for schema that work well with Qlik dashboards you should investigate star and snowflake schema.

in this case your requirement doesn't need seperate dimension tables as no unique dimensional attributes identified.  a dates a date its the fact it relates to that gives it context.  you could actually have a field called fact type and just a value field if you wanted to reduce the model some more.

give it a try.

valpassos
New Contributor III

Re: Not another mixed granularity fact tables...

Hi Andy!

I have applied your solution of concatenating it all, and it worked! At least for now

So, when you say that my dimension tables have no unique dimensional attributes identified, you are referring to fields that describe the table, right? For example, if I had a Customer table with lots and lots of attributes, like


Customer:

First Name

Last Name

Phone

Address1

Adress2

...

then, in that case, it would make sense to have a separate table, not concatenated to the facts but rather joined? Is that what you are saying?

Thanks Andy

Lisa

valpassos
New Contributor III

Re: Not another mixed granularity fact tables...

Hi Akshaya,

I have indeed concatenated it all and it seems to work Thanks for the suggestion!

Community Browser