Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
rileymd88
New Contributor II

Data Model Best Practice to Prevent Loops

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);

3 Replies
mark6505
Valued Contributor III

Re: Data Model Best Practice to Prevent Loops

Hi

how about this?

Mark

rileymd88
New Contributor II

Re: Data Model Best Practice to Prevent Loops

Hi Mark,

Thanks for your help. This works, however it is similar to what I have implemented so far (but a bit more cleaned up by creating one key). Is this the only way I can get my model to work? As I mentioned above I would like to stay away from having to use ApplyMap to populate the productDimension data as the relationships have been known to change frequently (which means I will always have to ensure I reload a lot of history to ensure the key is correct).

Of course this could be the only way due to the tricky scenario I have, but I just wanted to check this before building the document further.

mark6505
Valued Contributor III

Re: Data Model Best Practice to Prevent Loops

Hi,

Another option would looking to joining your product and target to you fact table.

So load the sales fact first, then join the Product table and finally the target.

Mark

Community Browser