Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a chart that is bringing in fees for accounts from two tables; Global fees and Hard Coded Fees. Every account is set up with a Global Fee structure based on numerous criteria, however, there are exceptions where the Global fees are overridden by fees which are hard coded in by the end user.
Furthermore, for the Global Fees, most accounts have at least two Global Fees attached to them, an Admin Fee & NO FEE. However, there are some exceptions where there is only one value returned under Global Fees which is NO FEE.
For the purposes of my Qlikview app, I need to just have one fee associated with each account with the following logic:
If there is a value under Hard Coded Fee, use that
If there is not a value under Hard Coded Fee, of the two values returned under Global Fee, use the one that is an Admin fee
If there is only value returned under Global Fee use that value
Please see the two charts below for an example of what I need to do:
Data currently is returned as:
Acct ID | Global Fee | Hard Coded Fee |
---|---|---|
100 | Admin 2% | - |
100 | NO FEE | - |
101 | Admin 1.25% | Admin 1.75% |
101 | NO FEE | Admin 1.75% |
102 | NO FEE | - |
103 | Admin 1.25% | - |
103 | NO FEE | - |
104 | Admin 1.75% | Admin 1.50% |
104 | NO FEE | Admin 1.50% |
What I need the data to return as:
Acct ID | FEE |
---|---|
100 | Admin 2% |
101 | Admin 1.75% |
102 | NO FEE |
103 | Admin 1.25% |
104 | Admin 1.50% |
Any help would be greatly appreciated.
Thanks,
-J D.
You could do it in your script like the following:
Global_Fees_Temp:
LOAD * INLINE [
Acct ID, Global Fee
100, Admin 2%
100, NO FEE
101, Admin 1.25%
101, NO FEE
102, NO FEE
103, Admin 1.25%
103, NO FEE
104, Admin 1.75%
104, NO FEE
];
//Hard_Coded_Fees:
LEFT JOIN (Global_Fees_Temp)
LOAD * INLINE [
Acct ID, Hard Coded Fee
101, Admin 1.75%
104, Admin 1.50%
];
Fees:
LOAD DISTINCT
[Acct ID],
MinString(if(not IsNull([Hard Coded Fee]), [Hard Coded Fee], [Global Fee])) as Fee
RESIDENT Global_Fees_Temp
GROUP BY [Acct ID];
DROP TABLE Global_Fees_Temp;
I've also attached an example file.
Hi,
Please Find the solution attached here with.
Regards,
Kaushik Solanki
Please see the attached example.
For data like this:
Global_Fees:
LOAD * INLINE [
Acct ID, Global Fee
100, Admin 2%
100, NO FEE
101, Admin 1.25%
101, NO FEE
102, NO FEE
103, Admin 1.25%
103, NO FEE
104, Admin 1.75%
104, NO FEE
];
Hard_Coded_Fees:
LOAD * INLINE [
Acct ID, Hard Coded Fee
101, Admin 1.75%
104, Admin 1.50%
];
You can create a straight table with:
Dimension: Acct ID
Expression: if(not IsNull([Hard Coded Fee]), [Hard Coded Fee], MinString([Global Fee]))
Thanks,
This works when I put it in a Straight Table but I should have clarified I'm using a Pivot Table as I have numerous dates that need to be shown as columns. The expression for the Fees column is causing issues with the layout. I'm wondering if I can get this new "Fees" column directly through the script? I've tried using a Resident Load and it looks good in the script but I'm getting an "Invalid Expression" error.
-J D.
You could do it in your script like the following:
Global_Fees_Temp:
LOAD * INLINE [
Acct ID, Global Fee
100, Admin 2%
100, NO FEE
101, Admin 1.25%
101, NO FEE
102, NO FEE
103, Admin 1.25%
103, NO FEE
104, Admin 1.75%
104, NO FEE
];
//Hard_Coded_Fees:
LEFT JOIN (Global_Fees_Temp)
LOAD * INLINE [
Acct ID, Hard Coded Fee
101, Admin 1.75%
104, Admin 1.50%
];
Fees:
LOAD DISTINCT
[Acct ID],
MinString(if(not IsNull([Hard Coded Fee]), [Hard Coded Fee], [Global Fee])) as Fee
RESIDENT Global_Fees_Temp
GROUP BY [Acct ID];
DROP TABLE Global_Fees_Temp;
I've also attached an example file.