Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
chiso_chiso
Creator
Creator

Finding a commission of a calculated value from an island table

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

6 Replies
jwjackso
Specialist II
Specialist II

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

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
MVP
MVP

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

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

jwjackso
Specialist II
Specialist II

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

Thank you Jerry. Worked like magic!!