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: 
qlikviewaf
Creator
Creator

left outer join ?? help!!

Hello,

i have this database structure (Sales). 

I would like to assign a commission Percentage % to each row of my sales based on Data Range (based on invoice date) and based on territory when available.

So for example for Crowford i would like to have for territory A  a percentage = 10%, for the others, since nothing is specified on the "commission" table, the percentage should be 20%.

So generally speaking, if on the commission table is specified a territory, join by SalesRep&Territory, if not use only the SalesRep generic percentage.

Anyone can please help on this?

Thank you so much!

 

saddsadsa.PNG

4 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hello,

(slight change to the code because I noticed you want to match by Sales Rep and Territory as well)

If I understand correctly, you are trying to match a date to an interval. You can check the IntervalMatch() function here:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

Here is how I think your code should look like:

 

Commission:
LOAD
SalesRep
, [Territory]
, Percentage
, Date(Date#(DataRangeStart, 'DD/MM/YYYY')) as DataRangeStart
, Date(Date#(DataRangeEnd, 'DD/MM/YYYY')) as DataRangeEnd
;
LOAD * INLINE [
SalesRep, Territory, Percentage, DataRangeStart, DataRangeEnd
Crowford, A, 10%, 01/01/2019, 31/12/2019
Crowford, C, 20%, 01/01/2019, 31/12/2019
Jonny, A, 30%, 01/01/2019, 31/12/2019
];


SalesTable:
LOAD
SalesRep
, [Territory]
, Amount
, Date(Date#([Invoice Date], 'DD/MM/YYYY')) as [Invoice Date]
;
LOAD * INLINE [
SalesRep, Territory, Amount, Invoice Date
Crowford, A, 15, 21/09/2019
Crowford, B, 18, 21/09/2019
Crowford, C, 20, 24/09/2019
Crowford, D, 51, 21/09/2019
Crowford, E, 45, 30/09/2019
Jonny, A, 48, 30/09/2019
Jonny, B, 45, 21/09/2019
Jonny, C, 21, 21/09/2019
];

LEFT JOIN (SalesTable)
IntervalMatch ( [Invoice Date], SalesRep, Territory )
LOAD DISTINCT
DataRangeStart
, DataRangeEnd
, SalesRep
, Territory
Resident Commission
;

LEFT JOIN (SalesTable)
LOAD * Resident Commission;

DROP Table Commission;

 

Here is the outcome of this code:

image.png

 

Please let me know if this helps!

 

Kind regards,

S.T.

qlikviewaf
Creator
Creator
Author

Hi,

this is working, but a piece is missing. I would like to have also a generic row for commission: in the example below Crowford got 40% for all territory not equal to "A" since this latter value is specified.

How i can do this?   So there should be kind of hierarchy: if the territory is available, use the % by territory, if not use the generic one. 

Hope i explained myself.

Thank you

 

SalesRep, Territory, Percentage, DataRangeStart, DataRangeEnd
Crowford, A, 10%, 01/01/2019, 31/12/2019

Crowford, , 40%, 01/01/2019,31/12/2019
Crowford, C, 20%, 01/01/2019, 31/12/2019
Jonny, A, 30%, 01/01/2019, 31/12/2019
];

Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hello,

 

This would be a second logical operation. After you've populated the respective percentages, you want to check if they exist and if not - apply some default value (I presume you have such value). Here is how you can achieve this with the code I sent. Green is new code.
 


//Exammple for Territory default Percengates in a heirarchy
TerritoryPercengate:
MAPPING LOAD * INLINE [
Territory, DefaultPercentage
A, 10%
B, 20%
C, 30%
D, 50%
E, 60%
];

Commission:
LOAD
SalesRep
, [Territory]
, Percentage
, Date(Date#(DataRangeStart, 'DD/MM/YYYY')) as DataRangeStart
, Date(Date#(DataRangeEnd, 'DD/MM/YYYY')) as DataRangeEnd
;
LOAD * INLINE [
SalesRep, Territory, Percentage, DataRangeStart, DataRangeEnd
Crowford, A, 10%, 01/01/2019, 31/12/2019
Crowford, C, 20%, 01/01/2019, 31/12/2019
Jonny, A, 30%, 01/01/2019, 31/12/2019
];


SalesTable:
LOAD
SalesRep
, [Territory]
, Amount
, Date(Date#([Invoice Date], 'DD/MM/YYYY')) as [Invoice Date]
;
LOAD * INLINE [
SalesRep, Territory, Amount, Invoice Date
Crowford, A, 15, 21/09/2019
Crowford, B, 18, 21/09/2019
Crowford, C, 20, 24/09/2019
Crowford, D, 51, 21/09/2019
Crowford, E, 45, 30/09/2019
Jonny, A, 48, 30/09/2019
Jonny, B, 45, 21/09/2019
Jonny, C, 21, 21/09/2019
];

LEFT JOIN (SalesTable)
IntervalMatch ( [Invoice Date], SalesRep, Territory )
LOAD DISTINCT
DataRangeStart
, DataRangeEnd
, SalesRep
, Territory
Resident Commission
;


LEFT JOIN (SalesTable)
LOAD * Resident Commission;

DROP Table Commission;

SalesFullPercentage:
NoConcatenate
LOAD
*,
If(IsNull(Percentage), ApplyMap('TerritoryPercengate', Territory, 'n/a'),Percentage) as PercentageComplete
RESIDENT SalesTable
;

DROP TABLE SalesTable;

 

I hope this helps!

 

Kind regards,

Stoyan

Brett_Bleess
Former Employee
Former Employee

Did Stoyan's last post get you what you needed?  If so, do not forget to come back to your post and use the Accept as Solution button the post(s) that helped you get things working to give credit to Stoyan for the help and to confirm to others the solution worked in this use case.  If you are still working on things, leave an update on where things stand.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.