Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

If - Help

B_Y_ID8
7 - Meteor

Hi all,

 

I am trying to introduce another part to my formula (enclosed as New Workflow 1) whereby:

 

if IsNull(Cat1) and IsNull(Cat2) Then

Check Category against Other1

Check Category against Other2

Check Category against Other3

Check Category against Other4

If Match Then Yes

Else No

 

For example on screenshot provided(Image): I would expect Row 8, 10, 13 and 14 to come back as Yes. Row 4 seems to be correct as doesnt match.

 

Any help please. I have been struggling to get the next part of the IF to be added to my existing IF statement.

 

Thanks

22 REPLIES 22
Maskell_Rascal
13 - Pulsar

Hi @B_Y_ID8 

 

Try this formula:

IF IsNull([Category])
	THEN 'No'
ELSEIF [Category] IN ([Cat1],[Cat2],[Other1],[Other2],[Other3],[Other4])
	THEN 'Yes'
ELSEIF ([Cat1]IN('DD') AND [Category]!='DD') || ([Cat2]IN('DD') AND [Category]!='DD')
	THEN 'DD Error'
ELSE 'No'
ENDIF

 

This lets the Category field find any match across the columns specified. 

 

Maskell_Rascal_0-1604089068262.png

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Thanks!

Phil

B_Y_ID8
7 - Meteor

Hi Phil,

 

Thanks for the response - it nearly does - apologies but I probably missed an extra bit out.

 

If content is already in Cat1 or Cat2 then it should not check Other1, Other2, Other3 and Other4. It should determine if Category matches either Cat1 or Cat2 and output Match Yes/No/DD Error.

 

Only if Cat1 and Cat2 are empty then the check of Category against Other1, Other2, Other3 and Other4 should take place and then output Match Yes/No.

 

That is the reason why I put: If isnull(cat1) and isnull(cat2) then check. (but I wasnt clear enough to begin with - my fault)

 

Hope this makes sense.

 

Thanks

B_Y_ID8
7 - Meteor

Hi Phil,

 

Ive enclosed the workflow again and demonstrated examples. So row 8, 10, 13 and 14 should be Yes going off the second IF part.

 

However row 16, 17 and 18 should be No as data is available in Cat1 or Cat2.

 

Hopefully that clears it up.

 

Thanks

Maskell_Rascal
13 - Pulsar

@B_Y_ID8 

 

This should work for you.

IF IsNull([Category])
	THEN 'No'
ELSEIF [Category] IN ([Cat1],[Cat2])
	THEN 'Yes'
ELSEIF 'DD' IN ([Cat1],[Cat2]) AND [Category]!='DD'
	THEN 'DD Error'
ELSEIF IsNull([Cat1]) AND IsNull([Cat2]) AND [Category] IN ([Other1],[Other2],[Other3],[Other4])
	THEN 'Yes'
ELSE 'No'
ENDIF

 

I modified the formula to only look if for Cat1 and Cat2 are null and after all the other operations first run. 

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Thanks!

Phil

B_Y_ID8
7 - Meteor

Hi Phil,

 

On the whole - Yes the solution has worked. Thank you.

 

I may need to check though as whether I need to apply as a logic - an example below:

 

Category   Cat1   Cat2

AA             DD      FF

= DD Error (which I would expect)

 

Category   Cat1   Cat2

AA             AA      DD

= Output shows as Yes (as it matches Category and Cat1) however I need to check if Cat2 would overrule and output should be DD Error.

 

At the moment I am happy with the solution and will mark as solved but if I do need to apply that logic would I be able to come back to you?

 

Thanks for your help - much appreciated.

atcodedog05
22 - Nova
22 - Nova

Hi @B_Y_ID8 

 

Modify the elseif like below. I am changing the order.

 

IF IsNull([Category])
	THEN 'No'
ELSEIF 'DD' IN ([Cat1],[Cat2]) AND [Category]!='DD'
	THEN 'DD Error'
ELSEIF [Category] IN ([Cat1],[Cat2])
	THEN 'Yes'
ELSEIF IsNull([Cat1]) AND IsNull([Cat2]) AND [Category] IN ([Other1],[Other2],[Other3],[Other4])
	THEN 'Yes'
ELSE 'No'
ENDIF

 

Please check and let me know. Priority is changed when order is changed it first come first serve 

 

In the previous IF

ELSEIF [Category] IN ([Cat1],[Cat2])
THEN 'Yes'

 

was checked first in your second case it was true hence it didnt check for DD error

 

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

B_Y_ID8
7 - Meteor

Hi atcodedog05,

 

Yes I have changed the IF sequence and it covers the scenario if Cat2 would overrule as mentioned and output a DD error.

 

Im not entirely sure which logic will be applied yet - however many thanks for giving the solution to the 2nd option.

 

Im not sure if I am able to accept both Yours and Maskell-Rascal solutions so that you both get credit. I have given Likes to the both of you though. Im hoping to be fair so when I find out which logic will be applied I will then accept that as the solution.

 

Again thank you to both of you!

atcodedog05
22 - Nova
22 - Nova

Hi @B_Y_ID8 

 

You will be able to mark both posts as solution

 

And do please check and let you know does it work or how can we help you further🙂

B_Y_ID8
7 - Meteor

Hi atcodedog05

 

Apologies for not posting the response. I have accepted both as solutions however in the end the chosen logic was what Maskell-Rascal wrote.

 

Many thanks

 

Labels