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

Compare 2 fields when one field is zero filled and the other is blank

Vaib
8 - Asteroid

I have to compare 2 fields using an if formula where field 1 is blank and field 2 is zero filled with 10 bytes (0000000000). So, when I compare field1 = field2 the value should be true. How do I convert the zero filled field to blank.

5 REPLIES 5
danilang
19 - Altair
19 - Altair

Hi @Vaib 

 

You can use a formula tool with

 

if [field2]="0000000000" then
     ""
else
     [field2]
endif

 

 

If you want to preserve the value of Field2 use something like

 

 

if [field2]="0000000000" and isempty(field1) then
	"true"
else
	tostring([field1])=[field2]
endif

 

 

 

Dan

ChrisTX
15 - Aurora

Are both data types String?

 

The easiest option would be to use a Filter tool with a formula like 

 

IF isEmpty([Field1]) and [Field2] = "0000000000" THEN 1 ELSE 0 ENDIF

 

Or use a Formula tool to create a new field, using the same formula above, with the new field's data type set to Boolean.

 

The "1" represents a boolean "True".

 

Chris

Vaib
8 - Asteroid

The formula still does not seem to be giving the appropriate results. Both the fields are string data types. One field is blank and the other field is zero filled with 10 zeroes 0000000000.

 

Field 1Field 2
 0000000000
12345678951234567895
 0000000000
 0000000000
 0000000000
 0000000000
 0000000000
96385274159638527415

 

I need to check for values where Field1 is not equal to Field2. 

ChrisTX
15 - Aurora

Which formulas have you tried on your own?  Did you use an OR operator?

 

A Filter tool with this code should help.

 

IF

(isEmpty([Field1]) and [Field2] = "0000000000") OR 

[Field1] = [Field2]

THEN 1

ELSE 0 ENDIF

 

Chris

Vaib
8 - Asteroid

Thank you everyone!

Labels