Connecting data sources with missing entries in fact table

Anonymous — Wed, 06 Dec 2017 14:53:29 GMT

Hello community,

I have several tables and my maintable is a classical fact-table with the sales for several fields which contain the following:

Of course there is not a sale in every store for every item/color/size combination per month - and this is where my problem starts.

I want to link another table that contains the monthly stock per item/color/size for every store - all combinations are filled here:

Date (Month) | store | Item_ID&Item_Color | Item_Size | Available Stock

I have to link the tables over a selfmade key, because otherwise I get a huuuuuge synthetic Key table.

Fact: Month&'_'&Store&'_'&Item_ID&Item_Color&'_'&Item_Size - some combinations are missing (no sales there)

Stock: Month&'_'&Store&'_'&Item_ID&Item_Color&'_'&Item_Size - all combinations are available, because everyone is filled with a stock-quantity or 0.

Because of the fact table having missing combinations (half or more) my sum of the available stock in the combined data-model is only half or less as if I load the stock.qvd in a single dashboard.

Is there any way to keep and connect all Available Stock values eventhough they sometimes have no counterpart in the fact table?

Thanks in advance.

Re: Connecting data sources with missing entries in fact table

Anonymous — Wed, 06 Dec 2017 15:55:28 GMT

Not sure what you are trying to achieve, but do you mean something like a left join?

Re: Connecting data sources with missing entries in fact table

marcus_sommer — Wed, 06 Dec 2017 15:59:00 GMT

I think I would just concatenate (union in sql) both tables - it's a very simple approach but it should work.

- Marcus

topic Re: Connecting data sources with missing entries in fact table in QlikView

Connecting data sources with missing entries in fact table

Re: Connecting data sources with missing entries in fact table

Re: Connecting data sources with missing entries in fact table