Multiple IFs logic not working properly as a Multibox

[#1]

Hi all,

Please can you assist.

I am currently trying to create a formula in QlikView that calculates the

Owners=If([Turnover Trade Type_]='REPO','Central Funding', If([Turnover Trade Type_]='Cash',If(Owner= 'Washbook',[Turnover Owner_],Owner),Owner))

The [Turnover Trade Type_] and [Turnover Owner_] are calculated in the edit script

[Turnover Trade Type_] = If(Right([Trade Type],8)='Movement','REPO','Cash')

and

[Turnover Owner_] = If([Issuer]='BANK ','GELP',If(RIGHT([Instrument Settlement],2)='V)' OR RIGHT([Instrument Settlement],2)='B)','Rates','Credit')) as [Turnover Owner_]

All of the fields come from one table (Volumes), however the Owner comes from a join (Volumes) joining onto the Gloss table (See below screenshot).

When I use the above formula in a Multibox for the user to select, it does not filter properly instead it selects all the variables that makes up the Owner.

The example belows the Current Selections when the Owners = Credit, as you can see the below is incorrect and the total does not reconcile back to the manually produced one.

Thank you very much

[kaushiknsolanki]

Hi,

Its always better to do all such calculations in script.

What you can do is use the join function or Applymap Function to get the Owner field in Volumes Table ,

Once the field is available in one table you can create your custom Owner field, which you can use in Listbox as filter.

Regards,

Kaushik Solanki

[#3]

Hi Kaushik,

I have tried doing a right join onto the table, however when I create the formula, it says that it does not recognise the Owner from the second table in the edit script.

Is there another way?

Thanks,

Mary

[kaushiknsolanki]

Hi,

Kindly hare the sample application or the script.

Regards,

Kaushik Solanki

[#5]

Thank you very much Kaushik for the prompt reply.

I will upload later

[#6]

Hi Kaushik,

When I update the script, I receive the following error because Owner is not recognised, even though it is now merged into the Raw Data table.

I have now commented the code out in Edit Script.

Please can you assist, I have also attached a copy of the QlikView application.

hank you very much

[rubenmarin · Accepted solution]

Hi, the error is showed because you're trying to use Owner before the table that loads that field, so the field doesn't exist when QV tries to check.

I have changed a bit your script. As I don't have sample data to load I cannot test, be aware that I have removed the right join so, if there is needed to check that the book exists, it will need some changes.

[#8]

Hi Ruben,

Many thanks for the script.

I have updated QlikView, however it is taking a while to load.

Is this normal?

The file I sent was a small extract of the original dashboard.

Thanks,

Mary

[rubenmarin]

Hi, not sure if that's normal, which part of the script takes time to execute?

Btw, I just copied the "If" sentence as it was, but now looking at it.. I don't really understand it, in example, this part:

If(If(Right([Type],8)='Movement','REPO','Cash')='REPO','Central Funding',

Can't it be resumed to?:

If(Right([Type],8)='Movement', 'Central Funding'

[Colin-Albert]

I would always aim to replace table that only has two fields with an ApplyMap function to eliminate the two field table.

This will give more consistent results than using joins.

Have a look at this post for more details of using ApplyMap and Mapping Load.  Don't join - use Applymap instead