Suggestion on data modelling

[madhuparnadhar]

Hello all,

I have one query regarding data model in one of my Qlik Sense apps. Below is a pictorial representation of what the current scenario is :

Orders and Returns table contain order/return ids, order/return status, order/return dates, etc. Orders and Returns are linked to each other based on Order Id.

Ordered Products, Returned Products and Replaced Products contain details related to orders/returns at an item level. All of these tables are fact tables in the data model.

Products is a dimension table and I need to link it to both Ordered Products and Returned/Replaced Products but struggling to figure out how to do so. Say if I select one product, I should be able to see ordered/returned quantities against it. What according to you would be the simplest way to achieve this without causing circular dependency/too much overhead in the data model ?

Thanks and Regards,

Madhuparna Dhar

[passionate]

Combine both fact tables return and order using left join
Left table will be order because it will always be subset.
Then link your product to new fact.
Advantage: you will end up with star schema

[pradosh_thakur]

Concatenate Ordered Products, Returned Products, Replaced Products to One table and add a new field table_flag. Table Falag would be 1,2,3 for  Ordered Products, Returned Products, Replaced Products table.

 Use table_flag  in Set analysis

load *, 1 as table_flag from Ordered Products;

concatenate

load *, 2 as table_flag from Returned Products;

concatenate

load *, 3 as table_flag from Replaced Products

[VishalWaghole]

Check how much fields are similar between order and replace table.
If both tables almost same then concatenate it with single table by creating flag as order and return.

If there are more different fields then crated link table which will only have common columns from Order, Replace and Product table .

Thanks and Regards,
Vishal Waghole

[madhuparnadhar]

Hi,

I had thought of one solution :  joining Orders with Ordered Products and Returns with Returned/Replaced Products. Then using a link table linking Orders, Returns and Products. But I am worried about the performance overhead that might result because of the join. 

Thanks and Regards,

Madhuparna Dhar