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

[#2]

Thanks Colin, Ruben has already included that in the example provided.

I will investigate and then get back to you.

Thanks for your time.

[#3]

Hi Ruben,

Yes, I have amended the formula to:

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

If(Owner= 'Washbook',

If([Turnover Issuer]='BANK','GELP',If(RIGHT([Turnover Instrument Settlement],2)='V)'

OR RIGHT([Turnover Instrument Settlement],2)='B)','Rates','Credit')),Owner),Owner));

However it still takes a long time.

I will investigate further then get back to you.

Once again thank you very much for your assistance, it has been invaluable.

[#4]

Hi Ruben,

I have amended the formula to the below, however it is not loading.

Please can you assist. Below, is also a screenshot of the table structure feeding into the dashboard.

Thanks,

If(Right([Turnover Trade Type],8)='Movement','Central Funding',If(Owner= 'Washbook',If([Turnover Issuer]='BANK','GELP',If(RIGHT([Turnover Instrument Settlement],2)='V)' OR RIGHT([Turnover Instrument Settlement],2)='B)','Rates','Credit')),Owner));

[rubenmarin]

Hi mary, sorry but I cannot give more help with that info, can you explain what means 'is not loading'? It gives an error?, the field is empty?

Btw, that field will need an alias, have you set that?:

If( ... ) as MyFieldName

[#6]

Hi Ruben,

Thank you very much for your prompt reply.

It is taking a very long time to load, more than an hour. Then I forced it to exit. 

Yes, I have updated the alias. The code works perfectly in the example provided but not in the actual.

Thanks,

Mary

[rubenmarin]

Hi Mary, is hard to say a solution without testing or taking a look at the reload log... where it hangs? In the table with the applymap? the table with the 'If'? at the end of script?

Try setting as comment the new lines with applymap and the 'If' to check if some of those are causing the delay. I would try first with the If, then with applymap, and a last try with both commented.

Maybe there is some 'join' sentence affected with the new fields or in hangs at the end of script trying to create synthetic keys... or any other cause.

[#8]

Hi Ruben,

Thank you very much for your reply and assistance.

It is working now, I used th Apply map directly in the formula, as per the below:

If(Right([Trade Type],8)='Movement','Central Funding',If(ApplyMap('MAP_Gloss', Book, null())= 'Washbook',If([Issuer]='ROYAL BANK OF CANADA','GELP',If(RIGHT([Instrument Settlement],2)='V)' OR RIGHT([Instrument Settlement],2)='B)','Rates','Credit')),ApplyMap('MAP_Gloss', Book, null()))) as NEW_OWNER,
ApplyMap('MAP_Gloss', Book, null()) as Owner,

Thanks again

Mary