topic Re: Combining tables and excluding values based on a specific heirarchy in QlikView

Combining tables and excluding values based on a specific heirarchy

Wed, 02 Nov 2016 17:12:06 GMT

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.

Re: Combining tables and excluding values based on a specific heirarchy

kaushiknsolanki — Wed, 02 Nov 2016 17:37:48 GMT

Hi,

Please Find the solution attached here with.

Regards,

Kaushik Solanki

Re: Combining tables and excluding values based on a specific heirarchy

Nicole-Smith — Wed, 02 Nov 2016 17:40:28 GMT

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

Re: Combining tables and excluding values based on a specific heirarchy

Wed, 02 Nov 2016 21:23:56 GMT

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.

Re: Combining tables and excluding values based on a specific heirarchy

Nicole-Smith — Thu, 03 Nov 2016 15:40:34 GMT

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.