topic Re: help on key table in QlikView

help on key table

Fri, 25 Dec 2015 07:03:12 GMT

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

Re: help on key table

oknotsen — Fri, 25 Dec 2015 09:43:50 GMT

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?

Re: help on key table

Fri, 25 Dec 2015 11:44:35 GMT

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.

Re: help on key table

Fri, 25 Dec 2015 11:57:03 GMT

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

Re: help on key table

asgardd2 — Fri, 25 Dec 2015 12:41:47 GMT

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)

Re: help on key table

Anonymous — Fri, 25 Dec 2015 14:31:19 GMT

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]

Re: help on key table

Mon, 28 Dec 2015 06:49:51 GMT

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