topic Re: left outer join ?? help!! in QlikView

left outer join ?? help!!

qlikviewaf — Mon, 21 Oct 2019 10:56:20 GMT

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!

Re: left outer join ?? help!!

Stoyan_Terziev — Mon, 21 Oct 2019 11:41:51 GMT

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/ScriptPrefixes/IntervalMatch.htm

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:

Please let me know if this helps!

Kind regards,

S.T.

Re: left outer join ?? help!!

qlikviewaf — Mon, 21 Oct 2019 12:17:06 GMT

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

Re: left outer join ?? help!!

Stoyan_Terziev — Mon, 28 Oct 2019 16:30:56 GMT

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

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

Re: left outer join ?? help!!

Brett_Bleess — Wed, 30 Oct 2019 15:57:59 GMT

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