I am new to data modelling and Qlik. I have got to grips with how to work with it but am struggling to get data i have been given into the right model.
I have some data that comprises 2 simple fact tables (Sales , Estimated Demand). Both of these tables contains a Product_Id, Store_id and then a value. The store_id is the link to a location dimension table for 4 stores A,B,C and D - each of which has a lat, long.
Small example for data/ columns names is:
I want to load the data into a model so that both the sales and demand data is linked on the store_id.
ie. So that i can have a sheet with charts for both demand and supply such that I can select store_id and have the graphs for demand and sales both update for the selected stores - and subsequently also show the selected store locations on map.
However, I'm struggling to understand the right model to do this.
It would seem to mean that I should have BOTH the demand and sales fact tables linked to the store_location dimension table which I understand is not a correct thing to do.
Any suggestions would be very gratefully received.
Re: One location dimension table with 2 fact tables?
Have marked Bruno's answer as correct as it was first and covered both scenarios of how to keep 2 tables or merge to one plus also the conversion to week.
However, both Jonathan and Sangram's answers were also correct and also added some extra information and tips which I also found useful so have marked both these as helpful. Its a shame only 1 answer can be marked as "correct"!