Combining tables and excluding values based on a specific heirarchy
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:
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.