Nested - If..Then..Else statement

hi, I am unable to get the correct results for the below code. The nested IF statement does give the correct results. Can someone help me identify the incorrect part.

IF [field1] = "PRICE" && IsNull([field2])

THEN

IF [field1] = "Qty" && I!sNull([field2])

THEN "REQUIRED FIELD"

ELSE "NOT REQUIRED"

ENDIF

ELSE "NOT FOUND"

ENDIF

Iam trying to validate data available in field1 and field2 to update a third field using this logic.

Developer

Developer Tools

Common Use Cases

Dynamic Processing

Accepted answers

All comments

DataNath

Hey @Bhanu04, it feels like you may want something like the following instead?

IF [field1] = "PRICE" && IsNull([field2])
THEN "NOT REQUIRED"
ELSEIF [field1] = "Qty"  && I!sNull([field2])
THEN "REQUIRED FIELD"
ELSE "NOT FOUND"
ENDIF

May have this the wrong way round but you can change if needed. The way your statement is currently set up, Alteryx is first checking that field1 = 'PRICE' and field2 is null. If that check is satisfied, it's then checking whether field1 = 'Qty', which it can't if it can't because it needs to equals 'PRICE' to get to the nested check.

Bhanu04

Actually, this wouldn't resolve the issue. But as you mentioned I am trying to look for 2 different fields in both the statements like values of a different row influences result of another row. Not sure how to resolve this.

DataNath

@Bhanu04 can you provide a sample of data and how you are expecting the outcome to look? If you're wanting to look across rows you'll need to use a Multi-Row Formula for starters but it's hard to know the exact requirement without an example. Thanks

Bhanu04

before

Field1 Field2 Field3

PRICE (Null) (empty)

Qty 60 ABC

after

Field1 Field2 Field3

PRICE (Null) REQUIRED FIELD

Qty 60 ABC

apologies unable to share real data but would want something like this.

DataNath

Thanks @Bhanu04, do you have examples for when you'd want the other flags i.e. NOT REQUIRED/NOT FOUND?

Deano478

@Bhanu04 it might not be of any help by you have a typo you have I!sNull([field2]) when it should be !IsNull([field2])

Bhanu04

NOT REQUIRED/ NOT FOUND are not specific use cases in my case.

There will be multiple IF statements each with its own rule, none of the previous logic will apply to the next one. Just the Field1 that is tackled once will not be considered again.

DataNath

So do you just need something like so?

IF [Field1] = 'Price' AND IsNull([Field2]) THEN 'REQUIRED FIELD' ELSE [Field3] ENDIF

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703