topic Re: Multiple IFs logic not working properly as a Multibox in QlikView

Multiple IFs logic not working properly as a Multibox

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

kaushiknsolanki — Tue, 15 Mar 2016 09:54:50 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

Tue, 15 Mar 2016 10:01:46 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

kaushiknsolanki — Tue, 15 Mar 2016 10:44:45 GMT

Hi,

Kindly hare the sample application or the script.

Regards,

Kaushik Solanki

Re: Multiple IFs logic not working properly as a Multibox

Tue, 15 Mar 2016 10:52:33 GMT

Thank you very much Kaushik for the prompt reply.

I will upload later

Re: Multiple IFs logic not working properly as a Multibox

Tue, 15 Mar 2016 17:08:57 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

rubenmarin — Tue, 15 Mar 2016 18:13:44 GMT

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.

Re: Multiple IFs logic not working properly as a Multibox

Wed, 16 Mar 2016 10:26:25 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

rubenmarin — Wed, 16 Mar 2016 18:07:47 GMT

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'

Re: Multiple IFs logic not working properly as a Multibox

Colin-Albert — Wed, 16 Mar 2016 18:14:27 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

Thu, 17 Mar 2016 16:44:14 GMT

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

I will investigate and then get back to you.

Thanks for your time.

Re: Multiple IFs logic not working properly as a Multibox

Thu, 17 Mar 2016 16:45:39 GMT

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.

Re: Multiple IFs logic not working properly as a Multibox

Tue, 22 Mar 2016 15:25:31 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

rubenmarin — Tue, 22 Mar 2016 17:55:20 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

Wed, 23 Mar 2016 08:41:23 GMT

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

Re: Multiple IFs logic not working properly as a Multibox

rubenmarin — Thu, 24 Mar 2016 11:55:12 GMT

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.

Re: Multiple IFs logic not working properly as a Multibox

Tue, 29 Mar 2016 13:12:37 GMT

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