if else endif condition, merging cells

[acerdell]

Hello,

I have two columns they both have 10 digit product numbers and I 'm trying to merge them into a single cell based on certain constraints as given below. I was able to do it with excel and VBA but was wondering if it is doable with the formula function in alteryx as it will serve better serve my purpose.

Thank you

1ST Product	2nd Product	Desired Outcome	Constriants
1010908184	0000000000	1010908184	If 2nd product has all 0s (always 10 digits), take the first product no
2688468446	8764348636	2688468446&8764348636	If both 1st and second numbers are different (other than all zeros), take them both and separate them with a &
0000000000	8646846912	8646486912	If 1st product has all 0s (always 10 digits), take the second product no
0000000000	0000000000	"    "	If both fields are all zeros, desired outcome is BLANK-EMPTY
1234567890	1234567890	1234567890	If 1st product number is as same as 2nd product number take only 1 instance as outcome

[Joe_Mako · Accepted solution]

How about a formula expression like:

IF [1ST Product]=[2nd Product] THEN
IF ToNumber([1ST Product])=0 THEN "" ELSE [1ST Product] ENDIF
ELSEIF ToNumber([1ST Product])=0 THEN [2nd Product]
ELSEIF ToNumber([2nd Product])=0 THEN [1ST Product]
ELSE [1ST Product]+"&"+[2nd Product] ENDIF

[acerdell]

Hmm, it did not work some reason..I replaced 0 with 10 digits 0s, still not showing a result

[Joe_Mako]

Can you attach an example of it not working?

In the attached, I copy-pasted your table, and it seems to be working.

What are your data types? I assumed "1ST Product" and "2nd Product" are strings.

[JordanB]

Hi @acerdell

Please find another workflow attached.

Best,

Jordan Barker

Solutions Consultant 

[acerdell]

Good call. I got it fixed it works perfect! Thanks