Nested - If..Then..Else statement

[Bhanu04]

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.

[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