Hi Experts!
I am trying to formulate a column using the reference formula from an excel template, but due to some reasons, the output is not what is expected.
This is in the excel- =IF(M2=12345;IF(S2<>0;S2;R2);IF(M2=67890;IF(S2<>0;S2;R2);IF(IF(B2="Sometext";R2;S2)<>0;IF(B2="Sometext";R2;S2);D2)))
This is what I did using formula tool- IIF([M1]=12345, IF [S1]!=0 THEN [S1.] ELSE [R1] ENDIF, IIF([M1]=67890, IF [S1]!=0 THEN [S1] ELSE [R1] ENDIF, IIF(IF [Sub Category]='Sometext' THEN [R1] ELSE [S1]!=0 ENDIF,[S1], [D1])))
Would you be able to help? Thanks
Regards.
Solucionado! Ir para Solução.
I think it should be this
IIF(M2=12345,IIF(S2!=0,S2,R2),IIF(M2=67890,IIF(S2!=0,S2,R2),IIF(IIF(B2="Sometext",R2,S2)!=0,IIF(B2="Sometext",R2,S2),D2)))
IF(X;Y;Z) in excel becomes IIF(X,Y,Z) in Alteryx
So you need to check the data types in your comparisons. I think m2, s2 and r2 need to be numeric types and b2 and d2 string types.
Hi,
I believe you will answer for your query
if ([M2]= 12345) then (if [S2] != 0 then [S2] else [R2]
endif) else (if [M2]=67890 then (if ([S2]!= 0) then [S2] else [R2] endif) else (if (if([B2 ]='Sometext')then [R2] else [S2] endif) != 0 then (if([B2 ]='Sometext')then [R2] else [S2] endif) else[D2] endif)endif) endif
I just did that and the error is gone now. However, D2 is not referred, if conditions are not met from S2. Some cells contain numbers in D2, which I would want to be picked when S2 is [Null]. It's not doing that. Data type issue?
Another thing that might help you are the ToString and ToNumber formula methods.
I just added another column and used if isnull, isnumber to get the desired output. Thanks for all the help!