topic Re: Linktable Nightmares... in App Development

Linktable Nightmares...

ijcornish — Wed, 17 Jan 2018 23:37:09 GMT

QlikSense...

I am struggling with Multiple Facts in the traditional Sales/Orders/Budget type model.

I have my tables of Orders & Sales with psuedo-key of Day, Product, Currency, Country & Customer (Key_Fact_Sales)

I have my Budget table, which is month, with pseudo key of Month, Product, Currency & COuntry. (Key_Fact_Budget)

These are concatenated into a link table.

I have separate dimension tables for Time (Time has Day, Month, Year etc), Product, Customer, Country & Currency.

Sales + Orders works fine.

However, when I add in Budget to the table, no data is returned - I think this is because of the different Key in the link table.

How best to resolve this?

I have thought of adding Key_Fact_Budget keys to the Sales/Orders tables, but this would imply that when I look at YTD performance vs annual budget, the budget calcs would only consider those periods where there are corresponding Sales.

Any suggestions

Re: Linktable Nightmares...

petter — Thu, 18 Jan 2018 06:18:10 GMT

Have you follow the guidelines outlined here: Concatenate vs Link Table

You say that you concatenate to create a link table. That doesn't sound right to me. A CONCATENATE LOAD would not create the link table you need.

There is an open source project called QlikView Components which to a large degree can be used in Qlik Sense also. Among the functions covered in this library is one that create a link table for you:

GitHub - RobWunderlich/Qlikview-Components: A library for common Qlikview Scripting tasks

Re: Linktable Nightmares...

ijcornish — Thu, 18 Jan 2018 07:35:39 GMT

Thanks Petter,

I have concatenated my pseudo keys into a link table, using the approach outlined... changing it to not concatenate took me a step forwards, in that current month budget now comes through...

However, i still seem to be unable to get budget numbers for months where there are no orders or sales.

Re: Linktable Nightmares...

petter — Thu, 18 Jan 2018 13:55:04 GMT

You could populate your Sales and Orders tables with rows containing null values or 0's in the metrics fields for these months.

Re: Linktable Nightmares...

ijcornish — Thu, 25 Jan 2018 14:53:42 GMT

The solution I found for this issue was to have a Sales & Orders Fact table with a link table to the dimensions.

The Budget Fact connected directly to the dimensions due to the different granularity of the data (month vs day)