Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

Data currently is returned as:

Acct IDGlobal FeeHard Coded Fee

100

Admin 2%-
100NO FEE-
101Admin 1.25%Admin 1.75%
101NO FEEAdmin 1.75%
102NO FEE-
103Admin 1.25%-
103NO FEE-
104Admin 1.75%Admin 1.50%
104NO FEEAdmin 1.50%

What I need the data to return as:

Acct IDFEE
100Admin 2%
101Admin 1.75%
102NO FEE
103Admin 1.25%
104Admin 1.50%

Any help would be greatly appreciated.

Thanks,

-J D.

1 Solution

Accepted Solutions
Nicole-Smith

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.

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Please Find the solution attached here with.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Nicole-Smith

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

Not applicable
Author

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.

Nicole-Smith

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.