Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gileswalker
Creator
Creator

Table not found - Resident

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:

 

ActualsAllocations2.jpg

 

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:

FirstScript.jpg

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:

error 1.jpg

 

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

resident2.jpg

 

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:

error2.jpg

 

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

 

Labels (2)
1 Solution

Accepted Solutions
gileswalker
Creator
Creator
Author

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.

mappingload.jpg

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 

applymap.jpg

This has achieved the goal.

I hope this helps someone.

View solution in original post

2 Replies
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
Creator
Creator
Author

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.

mappingload.jpg

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 

applymap.jpg

This has achieved the goal.

I hope this helps someone.