topic Re: Big Nested Ifs formula in App Development

Big Nested Ifs formula

SingSing16 — Thu, 20 Apr 2023 21:19:43 GMT

Hello,

Can you please support me with creation of big formula which will marge 4 small below:

=if(match(("CPS",'Backl') and match("OS",'UnQual','Qual','Prop','Neg','CA') or match("OS",'OW','Book','CR','CW'),'FALSE','TRUE'))

=if(match(("CPS",'Quot') and match ("OS",'UnQual,'Qual','Prop','Neg')or match("OS", 'OW','Book','CR','CW','CA')

,'TRUE','FALSE'))

=if(match(("CPS",'PTB','Shipp','Isnt','Accept','Clin') and match("OS",'UnQal','Qual','Prop','Neg','OW','Book','CR','CA') or match ("OS",'UnQal','Qual','Prop','Neg'), 'FALSE','TRUE'))

=if(match(("CPS",'ORP','IRP') or Match("OS",' '),'Invalid','Invalid'))

Best Regards,

Karolina

Re: Big Nested Ifs formula

edwin — Fri, 21 Apr 2023 19:06:05 GMT

if you explain what you are trying to do, what your data looks like then it may be easy for others to help you

Re: Big Nested Ifs formula

SingSing16 — Wed, 26 Apr 2023 16:15:06 GMT

Hello Edwin,

Thank you so much for your reply.

I think that the best way will be attatching the matrix, where you will see which answer I would like to gain.

I will really appreciate your help.

Re: Big Nested Ifs formula

edwin — Wed, 26 Apr 2023 17:59:45 GMT

if you want to maintaing the IF statements then you just cascade them, however you need to reformat these in the following :

if( condition1, Invalid,

if(condition1, True,

else False

)

)

can you tell me if the two fields CPS and OS are in one table? if so, you can build the logic in your DM by creating a table:
CPS, OS, Status
IPR, UnQual, Invalid
IPR, Qual, Invalid
...
Quot,UnQual, True
Quot, Qual,True
...

then you dont need any if statements. but that will depend on your DM. if CPS and OS are in separate tables, it becomes a little bit complicated but at least you dont need to maintain that head scratcher and your charts will be a little bit faster as it will not need to evaluate a cascaded if statement. also, the NULL will need a little tweaking

Re: Big Nested Ifs formula

SingSing16 — Thu, 27 Apr 2023 12:32:02 GMT

Hello Edwin, currently they are in the same table. I will try to do this according with your advice.

I will check and let you know how things are going.

Best Regards,

Karolina

Re: Big Nested Ifs formula

edwin — Thu, 27 Apr 2023 13:52:18 GMT

if they are in the same table then you can precalculate the status, yo can load your matrix as a table and then left join it to your table, this will give you your status as a field. you wont need your IF statement anymore which would make it faster and simpler to maintain