Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Concatenate the three tables together.
Source
GrandFather
Father
Child
GrandChild
RandomPerson1
RandomPerson2
RandomPerson3
Date
Fact1
Fact2
Fact3
Fact4
Regards
Andy
Hi Lisa,
Can you share your data model here. Did you try using concatenating three facts together.
Regards,
Akshaya
Concatenate the three tables together.
Source
GrandFather
Father
Child
GrandChild
RandomPerson1
RandomPerson2
RandomPerson3
Date
Fact1
Fact2
Fact3
Fact4
Regards
Andy
Looks like you have to use
Hierarchy and Hierachy belongs to. This creates a linkage between every Parent and Child.
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
Hi Lisa,
I don't see any such challenges by concatenating data.
Regards,
Akshaya
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
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.
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
Hi Akshaya,
I have indeed concatenated it all and it seems to work Thanks for the suggestion!