Hi,
I am having difficulties setting up a data model as I was continuously getting loop errors. I have 4 different tables which I am loading at the moment which you can see below.
The goal of this application is to be able to see the sales per product ID (Quantity field from salesFact) and also compare these sales against the sales target (Target Quantity from salesTarget).
The only way I have got this to work so far is creating a mapping load and mapping the Product Group 1 and Product Group 2 into the salesFact table, however this does not feel like the best approach as the Product ID -> Product Group 1 & 2 relationships are known to change so this means I will often have to continuously map historical data which would not lead to QVD optimized loads.
Does anyone have a better approach for my data model? I have attached a sample excel file and a test QVD document.
| salesFact: |
| Month |
| Country |
| Product ID |
| Quantity |
| productDimensions: |
| Product ID |
| Product Family |
| Product Group 1 |
| Product Group 2 |
| Product Supplier |
| calendarDimensions: |
| Month |
| Quarter |
| Year |
| salesTarget |
| Month |
| Country |
| Product Group 1 |
| Product Group 2 |
| Target Quantity |
My Script so far:
// Mapping tables for Product Groups
productGroupOneMap:
Mapping LOAD [Product ID],
[Product Group 1]
FROM
[QlikDMTest.xlsx]
(ooxml, embedded labels, table is productDimensions);
productGroupTwoMap:
Mapping LOAD [Product ID],
[Product Group 2]
FROM
[QlikDMTest.xlsx]
(ooxml, embedded labels, table is productDimensions);
calendarDimensions:
LOAD Month,
Quarter,
Year
FROM
[QlikDMTest.xlsx]
(ooxml, embedded labels, table is calendarDimensions);
productDimensions:
LOAD [Product ID],
[Product Family],
[Product Group 1],
[Product Group 2],
[Product Supplier]
FROM
[QlikDMTest.xlsx]
(ooxml, embedded labels, table is productDimensions);
salesFact:
LOAD Month,
Country,
[Product ID],
ApplyMap('productGroupOneMap', [Product ID], null()) as [Product Group 1],
ApplyMap('productGroupOneMap', [Product ID], null()) as [Product Group 2],
Quantity
FROM
[QlikDMTest.xlsx]
(ooxml, embedded labels, table is salesFact);
salesTarget:
LOAD Month,
Country,
[Product Group 1],
[Product Group 2],
[Target Quantity]
FROM
[QlikDMTest.xlsx]
(ooxml, embedded labels, table is salesTarget);
