Synthetic key in data model

[lucasdavis500]

I'm developing a data model for my dashboard, I've created dummy variables for most of the metrics I need, as well as month/year/quarters based on some date fields within each table. The date fields are now linked causing a synthetic key, because I didn't give a unique name for Month/Year/Quarter. The reason I didn't is because none of my tables have a PKEY/FKEY to link them. Each of my three "fact" tables is a data island, and two of them have lookup tables connected to them. The issue is that I have to create a dashboard, which will have at least a few graphs that "summarize" my data across the three tables. So I need to be able to summarize data over month/year/quarters, etc to get the appropriate value for the time dimension, without a Pkey. Here's a pic of the model:

Would the best way to go about this be to give a recno() to each table when I do my LOAD statement, and then give unique names to all of my fields that have a date? I'm confused about how QV will treat this, if I have date fields with different names, QV won't know how to summarize my fields properly across time dimensions (I think), but if I keep the field names the same, I have 3 synthetic keys... is that an issue? Right now, my "overview" would be the summarized data, so row-by-row matches aren't necessary. Any row-by-row analytics would need to be done within(inside) each table, which has the unique ID's, so I don't see that being an issue.

[rupamjyotidas]

Try Autonumber

Ex:

AutoNumber(Date,Id) as RNo

[maxgro]

You can find some interesting documents on data model  (thanks to marcus_sommer‌) here

Get started with developing qlik datamodels

look at the docs

synthetic keys

concatenate vs link table

fact table with mixed granularity

[lucasdavis500]

I have looked at auto number, but how do i solve the issue of the dates? Can i qualify my tables? If i have a chart of summarized data will qlik know that when i click '2016' i want 2016 from ACDV, CET and AUD Tables if the tables are qualified? I'm not sure how you treat a dates, i.e. month, year, quarterswithin each table so qlikview knows to associate that on my graphs with the appropriate date fields..

[Anonymous]

Use Qualify *;

And Unqualify for the keys that you want to relationships.

[lucasdavis500]

If I create an AutoNumber, based on each PKEY on each respective table, I think QV will do an OUTER JOIN automatically, correct? So that all of my data is present from all three tables?, However, This does not solve what to do with the date so that my summaries are reflected appropriately across time dimension..

[Anonymous]

Please, try this:

[johnw]

If the application is currently behaving as you wish, then the synthetic key in and of itself is not an issue. However, it is a hint that maybe something could be done better.

In my data models, a Month field is actually a date field, the first day of that Month, formatted usually like "Oct 2016". If that's true of your data, then I would create a calendar table with fields Month, Quarter, and Year, and only load the Month field in your fact tables. That would probably behave exactly the same, but I'd consider it to be cleaner.

If the application is NOT behaving as you wish, though, I'm not sure what to suggest because I'm not sure exactly what behavior you'd want. If the calendar fields have different names in each table, you are correct that QlikView will not connect them together in any automatic way in charts, and things get complicated and inefficient if you're trying to connect them together outside of the data model. It's doable. Might even be what you need. But I doubt it's what you need.

[Anonymous]

You could consider concatenating your 3 central Fact Tables together aiming for a classic star schema.