Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello community,
I have several tables and my maintable is a classical fact-table with the sales for several fields which contain the following:
Date (timestamp) | Date (Month) | store | Item_ID&Item_Color | Item_Size | Sales Volume ... and many more
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.
Not sure what you are trying to achieve, but do you mean something like a left join?
I think I would just concatenate (union in sql) both tables - it's a very simple approach but it should work.
- Marcus