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: 
valpassos
Creator III
Creator 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
Partner - Master II
Partner - Master II

Concatenate the three tables together.

Source

GrandFather

Father

Child

GrandChild

RandomPerson1

RandomPerson2

RandomPerson3

Date

Fact1

Fact2

Fact3

Fact4

Regards

Andy

View solution in original post

12 Replies
adityaakshaya
Creator III
Creator III

Hi Lisa,

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

Regards,

Akshaya

ogster1974
Partner - Master II
Partner - Master II

Concatenate the three tables together.

Source

GrandFather

Father

Child

GrandChild

RandomPerson1

RandomPerson2

RandomPerson3

Date

Fact1

Fact2

Fact3

Fact4

Regards

Andy

vkish16161
Creator III
Creator III

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
Creator III
Creator III
Author

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
Creator III
Creator III

Hi Lisa,

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

Regards,

Akshaya

valpassos
Creator III
Creator III
Author

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
Partner - Master II
Partner - Master II

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Hi Akshaya,

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