Finding a commission of a calculated value from an island table

[chiso_chiso]

Gurus,

I have a field 'Amount' in my fact which is populated daily,I am summing it for a week and want to find its Commission from an island table with ranges. Example attached

Message was edited by: Harrison Chisonga Loaded a qwv

[jwjackso]

Look at the IntervalMatch function https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

fact:

load * inline [

Salesman,WeeklySales

Mike,18

John,7

Luke,11

];

commission:

load * inline [

1,10,4

11,15,3

16,20,2.5

];

Inner Join IntervalMatch(WeeklySales)

load min,max

resident commission;

[chiso_chiso]

Hi Jerry,

The fact is already loaded in the QV as daily transactions. I am using an expression (=sum(amount)) with Dimension 'Week' from my calendar to arrive at those numbers. All i need is to reference the correct range from the island table to arrive at the right commission.

Please assist.

[MK_QSL]

Something like this..

IF(SUM(amount) >= 1 and SUM(amount) <= 10, 4,

IF(SUM(amount) >= 11 and SUM(amount) <= 15, 3,

IF(SUM(amount) >= 16 and SUM(amount) <= 20, 2.5)))

[chiso_chiso]

@Jerry @Manish See attached QVW uploaded to understand better. Please assist

[jwjackso]

I think you need to handle this in the load script.  See the attachment.

After the Fact load, I created a weekly sales file:

WeeklySalesTotal:
LOAD Salesman,
Sum(Amount) as WeeklySalesTotal
Resident Fact group by Salesman;

After the Commisssion load, I used the IntervalMatch function:

Inner Join IntervalMatch (WeeklySalesTotal)
LOAD Min,
Max
Resident Commissions;
DROP Table WeeklySalesTotal;

Fact_weekly:
LOAD Salesman,
sum(Amount) as WeeklySalesTotal
Resident Fact group by Salesman;
Left Join(Fact_weekly)
LOAD WeeklySalesTotal,
SalesCommission,
MarketingCommission
Resident Commissions;

DROP Table Commissions;

[chiso_chiso]

Thank you Jerry. Worked like magic!!