Table not found - Resident

[gileswalker]

Hi - A reasonably simple situation it seems - but I cannot seem to generate the correct code to resolve. I also can't find a similar enough example in the community that I can leverage. Hoping someone can assist please?

Situation: Our sales people sell units of stock and are measured on sales versus what they had allocated to them....to create a weekly gap analysis report.

Allocations of stock to sales people are normally done by planners in advance of the sales process, and there can be several changes to the allocated units up to the start of week the sales are performed. However allocations are not done 100% of the time. Our process means we have 3 general outcomes we have to report on:

• Stock Allocations get sold at some level (ie at, above or below allocation levels)
• We allocate stock, but no sales occur (by a specific sales rep)
• A sales rep sells, but they had no allocation

In my situation we are reviewing sales for 2019, week 20. During the planning phase, we have created 3 iterations of the allocations for John and David, firstly on 10/03/2019, secondly on 20/03/2019, and last/most recently on 1/04/2019. The history of allocations is not deleted (to facilitate a track changes process), and so the LOAD script needs to ensure that only the most recently dated allocation data is used to compare to actual sales. Here is a pictorial of the process I want to use:

So in this example above, we need:

- John and David: we need the allocations against Max(Data_Submission_Date) ie 1/04/2019 to be compared to sales.
- Jane and Les: we need the allocations against Max(Data_Submission_Date) ie 10/03/2019 to be compared to sales.
- Phil and Lucy: they have no allocation for this period

I have 2 variations of LOAD script and they are failing for different reasons:

The first version is this one:

As editable text it is:

Actuals:
LOAD
"Year",
Week_Number,
Contract_Number,
Name,
"Year" & Week_Number & Contract_Number & Name as uniquekey,
Units_Sold
FROM [lib://DataPark (mondiale_giles.walker)/Ideation Testing\IdeationTest5.xlsx]
(ooxml, embedded labels, table is Actuals);

Outer Join (Actuals)

LOAD
"Year",
Week_Number,
Contract_Number,
Name,
"Year" & Week_Number & Contract_Number & Name as uniquekey,
Units_allocated_for_sale,
Data_Submission_Date
FROM [lib://DataPark (mondiale_giles.walker)/Ideation Testing\IdeationTest5.xlsx]
(ooxml, embedded labels, table is Allocations);

Right Join

LOAD
"Year" & Week_Number & Contract_Number & Name as uniquekey,
Date(Max(Data_Submission_Date)) as Data_Submission_Date
Resident Actuals
Group By "Year" & Week_Number & Contract_Number & Name;

This produces data, but because the Resident is to 'Actuals' I am not getting all 6 sales reps pulling through - I only get 4 per below, sales reps Lucy and Phil are missing:

The second version is this one (the resident is different):

As editable text it is:

Actuals:
LOAD
"Year",
Week_Number,
Contract_Number,
Name,
"Year" & Week_Number & Contract_Number & Name as uniquekey,
Units_Sold
FROM [lib://DataPark (mondiale_giles.walker)/Ideation Testing\IdeationTest5.xlsx]
(ooxml, embedded labels, table is Actuals);

Outer Join (Actuals)

LOAD
"Year",
Week_Number,
Contract_Number,
Name,
"Year" & Week_Number & Contract_Number & Name as uniquekey,
Units_allocated_for_sale,
Data_Submission_Date
FROM [lib://DataPark (mondiale_giles.walker)/Ideation Testing\IdeationTest5.xlsx]
(ooxml, embedded labels, table is Allocations);

Right Join

LOAD
"Year" & Week_Number & Contract_Number & Name as uniquekey,
Date(Max(Data_Submission_Date)) as Data_Submission_Date
Resident Allocations
Group By "Year" & Week_Number & Contract_Number & Name;

This version errors on loading as follows:

I have tried a host of different things to make this work..... I just can't find the key to unlock this one, so hoping someone smarter than me is able to assist please?

Thanks Giles

[marcus_sommer]

I think there is no resident table Allocation - it's just an excel-sheet and if you want to get the max-date only from there you need to load from the sheet. But practically it means that you need just to change the order of your statements to something like this:

table:
load * from Allocations;
right join(table) load max-date resident table;
outer join(table) load * from Actuals;

But I wouldn't try it in this way because usually you could not simply join these tables - if there are any sales without allocations or reversed you will lose some information because there is no match.

Alternatives to this is creating a link- respectively a bridge-table between both fact-tables. This approach is quite common but often not the best choice. Another way is to identify the missing values in both fact-tables and adding then the appropriate values (key-values + zero or null() for the measures).

Much easier and usually my recommendation is just to concatenate both fact-tables (of course if you want to keep multiple allocation-states you need to flag them, maybe 0 for current and -1, -2, ... for the previous ones - and this flag could then be used within a listbox to select the wanted state and/or as a set analysis condition within your expressions).

- Marcus

[gileswalker · Accepted solution]

Thanks for your help. It helped me evaluate my options.

What I have ended up managing to conclude is only 1 workable way of proceeding.

Firstly create a mapping load, to retrieve the Max(Submission_Date), and with it creating a unique key.

The create the same key in the allocation data, and use applymap to pull the Max(Submission_Date) through, and applying its alias name as 'maximumDate'.......Then only load all where the Submission_Date = maximumDate 

This has achieved the goal.

I hope this helps someone.