Avoid Synthetic Keys using Link tables

[Arvind_07]

Hi All-

I have five tables loaded- FG-MTD DOM, FG-YTD DOM ,FG-MTD Aff, FG-YTD Aff , and Calendar

FG-MTD and FG-YTD has [Product] field in common

FG-MTD and Calendar has [Week Number] field in common

FG-YTD and Calendar has [Month] field in common

FG-MTD has the measures generated by Week numbers and FG-YTD has measures generated by Month.

Sheets with the suffix DOM has same measures(only difference is MTD has the measures grouped by week and YTD has the measures grouped by month) and Sheets with the suffix Aff has same measures(MTD grouped by Week and YTD grouped by month)

I have created a Link table in my load as mentioned below.

But I still see Synthetic keys in my Data model viewer due to the measure fields. Can anyone please help to fix this problem?

Objective is to calculate measures using set expressions so I can use both dimensions(Week number and Month) for calculating different measures like MTD sales(upto last week), CM Sales, YTD Sales and so on for both DOM and Aff

Attached the source file for reference.

This is my Load script:-

[FG- MTD DOM]:

LOAD [Product]&'|'&[Week Number] as [%Keyfield],
[Product ],
[Week Number],
[Forecast Units] AS [ DOM.Forecast Units],
[Sales Units] AS [DOM.Sales Units],
[Last year Sales Units ] AS [DOM.Last year Sales Units]
FROM [lib://AttachedFiles/Source-Qlik.xlsx]
(ooxml, embedded labels, table is [FG- MTD DOM]);

[FG- MTD Aff]:

LOAD [Product]&'|'&[Week Number] as [%Keyfield],
[Product ],
[Week Number],
[Forecast Units] AS [Aff.Forecast Units],
[Sales Units] AS [Aff.Sales Units],
[Last year Sales Units ] AS [Aff.Last year Sales Units]
FROM [lib://AttachedFiles/Source-Qlik.xlsx]
(ooxml, embedded labels, table is [FG- MTD Aff]);

[FG- YTD DOM]:

LOAD [Product]&'|'&[Month] as [%Keyfield],
[Product ],
[Month],
[Forecast Units] AS [DOM.Forecast Units],
[Sales Units] AS [DOM.Sales Units],
[Last year Sales Units ] AS [DOM.Last year Sales Units]
FROM [lib://AttachedFiles/Source-Qlik.xlsx]
(ooxml, embedded labels, table is [FG- YTD DOM]);

[FG- YTD Aff]:

LOAD [Product]&'|'&[Month] as [%Keyfield],
[Product ],
[Month],
[Forecast Units] AS [Aff.Forecast Units],
[Sales Units] AS [Aff.Sales Units],
[Last year Sales Units ] AS [Aff.Last year Sales Units]
FROM [lib://AttachedFiles/Source-Qlik.xlsx]
(ooxml, embedded labels, table is [FG- YTD Aff]);

[Calendar]:
LOAD [Week Number]&'|'&[Month] as [%Keyfield],
[Year],
Date#([Month],'MMM') as [Month],
Date(Date#([Date], 'YYYY-MM-DD') ) AS [Date],
[Week Number],
[Month End],
[Week number in a month],
[Week Day],
[Day],
[Month abbr],
[Week Number Normal]
FROM [lib://AttachedFiles/Source-Qlik.xlsx]
(ooxml, embedded labels, table is Calendar);

[Linktable]:
Load
Distinct
[%Keyfield],
[Product],
[Week Number]
Resident [FG- MTD DOM];

Concatenate([Linktable])
Load
Distinct
[%Keyfield],
[Product],
[Week Number]
Resident [FG- MTD Aff];

Concatenate([Linktable])
Load
Distinct
[%Keyfield],
[Product ],
[Month]
Resident [FG-YTD DOM];

Concatenate([Linktable])
Load
Distinct
[%Keyfield],
[Product],
[Month]
Resident [FG-YTD Aff];

Concatenate([Linktable])
Load
Distinct
[%Keyfield],
[Week Number],
[Month]
Resident [Calendar];

Drop Fields [Product],[Week Number] From [FG- MTD DOM];
Drop Fields [Product],[Week Number] From [FG- MTD Aff];

Drop Fields [Product],[Month] From [FG-YTD DOM];
Drop Fields [Product],[Month] From [FG-YTD Aff];

Drop Fields [Week Number],[Month] From [Calendar];

[rubenmarin · Accepted solution]

Hi @Arvind_07, maybe it's easier if you assign one date to each week or month, so all tables makes the relationship with calendar using this date field, the calendar table will tell wich week or month this date is related to.

Having all tables with date field you can concatenate all in one fact table related with calendar using the Date field, so ther will be no need of link tables.

To assign dates you can do a mapping from the calendar table to assign the first date of each week, and for month you can do the same or directly load as: Date(MonthStart(Date#([Month],'MMM')),'YYYY-MM-DD')  as Date.

Month and week will be loaded only in calendar table, fact tables will only load Date field to realte with calendar table.

[Arvind_07]

@rubenmarin  Thank you for this suggestion.  

But Still-

If you look at the YTD sheet- Month field contains cumulative sum of  entire month information on any particular month. So if I assign a Date field- it will be different than when I assign a Date field to the MTD sheet which contain Week numbers 

Thanks a lot for helping me out!

[rubenmarin]

Hi @Arvind_07, to control that I think it's beeter to rename the measure field, instead of [DOM.Sales Units] rename it to [DOM.Sales Units YTD] (for YTD table) and [DOM.Sales Units MTD] (for MTD tables).

If for some reason you don't want to rename measure, and alternative can be create a new field wich store the type of accumulation, something like: 'YTD' as AccumType (for YTD table) or 'MTD' as Accumtype (for MTD tables).