categories+nested if formulas

[EtamSoko]

hi Guys, 

I need a little help with one nested if formula in one of my workflow.

What I am trying to achive:

Categorizing based on service numbers, cat 1 and 2. this works fine.

Based on the categories I need the following:

Scenerio 1 : if category 1 > 2 then retrun 97

Scenario 2: if categroy 2 > 1 , then 3 other scenarios: if [Ctry VAT No.] = [Plant] THEN "2F" and [Ctry VAT No.] IN (B,C,D) THEN "4F"
and [Ctry VAT No.] IN (E,F,G) THEN "F9" . Also if Ctry VAT is null then i just want it to return value "blank"

Attached the simple file for this exercise.

Thanks very much!

MK

[JoeS]

Hi @EtamSoko 

I have done it using the formula:

IF [category 1] > [category 2] THEN "97"
ELSEIF IsEmpty([Ctry VAT No.]) THEN "blank"
ELSEIF [Ctry VAT No.] = [Plant] THEN "2F" 
ELSEIF [Ctry VAT No.] IN ("B","C","D") THEN "4F"
ELSEIF [Ctry VAT No.] IN ("E","F","G") THEN "F9"
ELSE "MissedCondition"
ENDIF

I think the main differences was that you had an "and" when you needed an "ELSEIF" and second you need to put quotes around text values in the IN statement

[danilang]

Hi @EtamSoko 

Your input file didn't have any category information, but here's the nested IF

if [Ctry VAT No.] = [Plant] THEN 
	"2F" 
elseif [Ctry VAT No.] IN ("B","C","D") THEN 
	"4F"
elseif [Ctry VAT No.] IN ("E","F","G") THEN 
	"F9"
else 
	"blank"
endif

Dan 

[EtamSoko]

hi @JoeS ,

Thanks for your help. Sorry for the late response - festive season passed.

The solution you proposed doesn't work because now, each time category 2 is bigger than 1, it gives me "MissedCondition" value instead of doing the logical checks.

Apologies if my explanation was not clear, let me try to describe it once more.

if Category 1 is >= 2 then "97" - any other case means then if 2 > then 1 there should be 3 outcomes: 

[Ctry VAT No.] = [Plant] THEN "2F"

[Ctry VAT No.] IN (B,C,D) THEN "4F"
[Ctry VAT No.] IN (E,F,G) THEN "F9" 

Regardless of the result what is bigger cat 1 or 2, if Ctry VAT No. is empty, then return "blank"

Can you please help me to get this result?

Thanks a lot!

MK

[JoeS]

Hi @EtamSoko 

I think I'd need to see some of you data to know what's happening.

Currently the only way that a missed condition is allocated is if the Ctry VAT No. is not equal to Plant or B, C, D, E, F, G.

Is that the case in your data? If so what does it then need to equal?

[EtamSoko]

@JoeS

The "MissedCondition" comes up even if Ctry VAT = Plant. The common reason for this to appear is that cat 2> 1.

I attached the part of the workflow.

I really appreciate your help on this.

Let me know please if you need anything else.

Regards,

MK

[JoeS · Accepted solution]

Hi @EtamSoko 

You're welcome! Thanks for uploading the examples.

Looking at the data I could see that there is a data quality issue with the Ctry VAT No. column, it contains trailing spaces:

Which means that Plant and Ctry VAT No. are not the same as one has a space the other doesn't.

I added a trim function to the formula before you used it:

Then I noticed it was still missing some conditions, and you appear to have missed of the first part of mine, so I added that back in:

That should do the trick, let me know if there is anything else it's missing.

[EtamSoko]

it works perfectly now, thanks a million!

[JoeS]

Hi @EtamSoko 

Great stuff, glad we got it all sorted in the end.

I hope you enjoyed your festive break too!