Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

categories+nested if formulas

EtamSoko
8 - Asteroid

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

 

 

8 REPLIES 8
JoeS
Alteryx Alumni (Retired)

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
19 - Altair
19 - Altair

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
8 - Asteroid

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
Alteryx Alumni (Retired)

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
8 - Asteroid

@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
Alteryx Alumni (Retired)

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:

Pic2.png

 

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:

 

Pic3.png

 

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:

Pic4.png

 

 

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

 

EtamSoko
8 - Asteroid

it works perfectly now, thanks a million!

JoeS
Alteryx Alumni (Retired)

Hi @EtamSoko 

 

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

 

I hope you enjoyed your festive break too!

Labels
Top Solution Authors