Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm confused about how to create a correct key table. Can anybody advise me on which fields I should use to create a key table on attached table diagram?
Thank you.
Firat
Year & '|' & MonthNo & '|' & ShopName as %YEAR_MONTH_SHOP
And then create a link table:
Key_table:
LOAD Distinct
FieldValue('%YEAR_MONTH_SHOP', IterNo()) as %YEAR_MONTH_SHOP
SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 1) as Year,
SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 2) as MonthNo,
SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 3) as ShopName
AutoGenerate (1)
While not IsNull(FieldValue('%YEAR_MONTH_SHOP', IterNo()));
Based on the current shape of the data model, I would start with joining a bunch of tables and take it from there.
How does the source data model look like?
The sources are just Excel files. Currently, it works without any problem. I don't have much database or programming knowledge, so I don't want to play with it a lot
I would like to replace the synthetic table with a key table. but I couldn't figure out which fields to concat to create a key or maybe keys ?
Thanks for your reply by the way.
Hello Firat,
I think the Data Model built by Qlikview should work without issue. Nevertheless, if you want to remove Syn generation you can have a look into the 2 scripts I attached. One is just reproducing same Data Model using 2 Keys One built with Year, MonthNo and ShopName and the other One for the Calendar built with Year and MonthNo.
The second script is another way over many others where instead of keeping all tables we try to build a central main table.
Wish you a Merry Christmas
Regards
Gilles
PS : Join the sample Data created for the purpose of the demo
Year & '|' & MonthNo & '|' & ShopName as %YEAR_MONTH_SHOP
And then create a link table:
Key_table:
LOAD Distinct
FieldValue('%YEAR_MONTH_SHOP', IterNo()) as %YEAR_MONTH_SHOP
SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 1) as Year,
SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 2) as MonthNo,
SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 3) as ShopName
AutoGenerate (1)
While not IsNull(FieldValue('%YEAR_MONTH_SHOP', IterNo()));
Your main Calendar Dimension Key looks like a composite of:
Year
MonthNo
I'd make this into a MonthYear field using the MakeDate() and MonthName() functions. You already have a MonthYear field in your MasterCalendar table.
Your other Dimension Key looks like ShopName. Plus you have Customer, but that only associates via ShopName.
+++++++++++++++++++++++
For the rest of the tables they look like Facts, all with the same key of $Syn 3.
These I would combine into a single Fact Table, using Joins, ApplyMaps or Concatenates, depending on your actual data.
+++++++++++++++++++++++
Thus I would end up with a classic Star Schema with :
A single central Fact Table
A Calendar Dimension Table
A Shop Dimension Table
A Customer / Shop Dimension Table [Not convinced about this table - you need to look at your source data / requirements]
Thanks Anton, this is how it looks now: (can be perfected but works for me)