Facttable

[SonPhan]

Hello Qlik Experts, 

i have a small problem:

The user has a problem where when selecting the Salesperson Code 1006, the value 100 should be displayed but this cannot work because the primary key %Sales_KEY_PK is empty. On the other hand, when selecting Source No HB1006, the user wants to see the values 300 and 400. Although the user could link both tables through Budget Code and SalesCode, the data model is more complex so a fact table was created between the two tables.

For example:

Faktentabelle:
Load * Inline [
%SALES_KEY_PK, %ITEM_BUDGET_CUSTOMER_PK, %SALES_CUSTOMER, Value, Value2, Source
"AI074417-10000-1", , HB1006-1, , 300, SALES
"AI074417-20000-1", , HB1006-1, , 400, SALES
,"HB1006-1121781-1", HB1006-1, 100, , BUDGET
];

DIM_SALES:
LOAD * INLINE [
%SALES_KEY_PK, "Salesperson Code", SALES_CODE
AI074417-10000-1, 1006, HB1006-1
AI074417-20000-1, 1006, HB1006-1
];

DIM_BUDGET:
LOAD * INLINE [
%ITEM_BUDGET_CUSTOMER_PK, SourceNo, BUDGET_CODE
"HB1006-1121781-1", HB1006, HB1006-1

];

Best regards Son

[marcus_sommer · Accepted solution]

I think the data-model isn't really suitable because Sales and Budget are in general the same data - just the direction is different - and should have a complementary data-structure and not a substituting one. Thinking further it meant to have a single fact-table and n dimension-tables whereby the dimensions aren't contrary like in your example else each one is a more or less granular description of a fact-key like dates, products, areas and so on.

Further it's quite normal that not all views are possible with all selections, for example by monthly budget-data (which could be added on a date-level by measures like makedate(Year, Month) as Date) and daily sales-data would selecting a single date exclude all budget-data. Within many scenarios that's not a problem because it's an intended and expected behaviour - none existing data couldn't be  displayed. But if there is any need to show such data they must be generated. Related to the above mentioned case the monthly data could be divided by the number of calendar-days or working-days which results in a daily value. This relates to all other kinds of granularity, too.

[SonPhan]

Thank you for your detailed answer, this helps me a lot!