Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

Re: left outer join ?? help!!

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.

Highlighted
Creator
Creator

Re: left outer join ?? help!!

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

Highlighted
Partner
Partner

Re: left outer join ?? help!!

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

Highlighted
Digital Support
Digital Support

Re: left outer join ?? help!!

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.