Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Trying once more as inital post was marked as spam 😞
I am trying to play around with the DB found online (link was here, removing not to trigger spam filter), this is how my current data model lookis like
Having trouble modelling it in Qlik properly, as I am getting a circular reference which I am unable to resolve, not loosing the analytical capabilities.
The problem is with "stocks" table, as it contains both product_id and store_id. What would be the best way to resolve this?
Here is the script:
[orders]:
LOAD order_id,
customer_id,
order_status,
order_date,
required_date,
shipped_date,
// store_id,
// staff_id,
store_id&staff_id as OrdersToStaffsKey;
SELECT * FROM orders;
[order_items]:
LOAD order_id,
item_id,
product_id,
quantity,
list_price as order_list_price,
discount;
SELECT * FROM `order_items`;
[products]:
LOAD product_id,
product_name,
brand_id,
category_id,
model_year,
list_price as product_list_price;
SELECT * FROM products;
Left Join (products)
LOAD brand_id,
brand_name;
SELECT * FROM brands;
Left Join (products)
LOAD category_id,
category_name;
SELECT * FROM categories;
Drop Fields brand_id, category_id;
[customers]:
LOAD customer_id,
first_name &' '&last_name as customer_name,
city as customer_city,
state as customer_state;
SELECT * FROM customers;
[staffs]:
LOAD staff_id,
first_name & ' ' & last_name as staff_name,
active as staff_active,
store_id,
manager_id,
store_id&staff_id as OrdersToStaffsKey
;
SELECT * FROM staffs;
[stores]:
LOAD store_id,
store_name,
street as store_street,
city as store_city,
state as store_state,
zip_code as store_zip_code;
SELECT * FROM stores;
[stocks]:
LOAD store_id,
product_id,
quantity as stocks_quantity;
SELECT `store_id`,
`product_id`,
quantity
FROM stocks;
you can combine order table :
[orders]:
LOAD order_id,
customer_id,
order_status,
order_date,
required_date,
shipped_date,
// store_id,
// staff_id,
store_id&staff_id as OrdersToStaffsKey;
SELECT * FROM orders;
Left Join ( orders )
//[order_items]:
LOAD order_id,
item_id,
product_id,
quantity,
list_price as order_list_price,
discount;
SELECT * FROM `order_items`;