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: 
Not applicable

help on key table

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

1 Solution

Accepted Solutions
asgardd2
Creator III
Creator III

If you don't want change a model, you must create a key in each table:

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()));

Feld  "ShopName" is missing in Calendar table, and we can  create  another key Year & '|' & MonthNo and put  it in the link table:
SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 1) & '|' & SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 2)

View solution in original post

6 Replies
oknotsen
Master III
Master III

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?

May you live in interesting times!
Not applicable
Author

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.

Not applicable
Author

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

asgardd2
Creator III
Creator III

If you don't want change a model, you must create a key in each table:

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()));

Feld  "ShopName" is missing in Calendar table, and we can  create  another key Year & '|' & MonthNo and put  it in the link table:
SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 1) & '|' & SubField(FieldValue('%YEAR_MONTH_SHOP', IterNo()), '|', 2)
Anonymous
Not applicable
Author

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]

Not applicable
Author

Thanks Anton, this is how it looks now: (can be perfected but works for me)

table.JPG