Please see the attached two files: File 1 is the original file and file 2 is what we want. For Alabama, we just need to put app under 1020 and 1047 and the else are zero; for Arkansas, we just need to put app under 1020 and 1048 and the else are zero. I'm wondering what kind of formula we should write. Appreciate your time! Thanks!
Solved! Go to Solution.
Hi @Hongli1216 ,
You can create a IF condition to get that result.
if [Jurisdiction]='Alabama' and ([CoCd]=1020 or [CoCd]=1047)
then [App]
elseif [Jurisdiction]='Arkansas' and ([CoCd]=1020 or [CoCd]=1048)
then [App]
else 0
endif
Best,
Fernando Vizcaino
Hi @Hongli1216, the following formula might help you achieve your output:
IF [Jurisdiction] = 'Alabama'
AND [CoCd] = 1020 or [CoCd] = 1047
THEN [App]
ELSEIF [Jurisdiction] = 'Arkansas'
AND [CoCd] = 1020 or [CoCd] = 1048
THEN [App]
ELSE 0
ENDIF
Hi Fernando,
Thanks for your solution! Please see the attached file, actually I want to keep WI's app filed as Con. If there is no filing method as Con, your formula works perfectly. I have more than two states as Sep filing method, can I just put the rest of them after Arkansas? Please advise! Thanks!
Hi @Hongli1216,
You could use the following formula:
IF ([Jurisdiction] = 'Alabama' AND [CoCd] IN(1020,1047))
OR ([Jurisdiction] = 'Arkansas' AND [CoCd] IN(1020,1048))
OR ([Jurisdiction] = 'Georgia' AND [CoCd] IN(1048))
OR ([Filing Method] = 'Con')
THEN [App]
ELSE 0
ENDIF
If you want to add further jurisdictions you can simply add a new line in the formula as follows (4th line as an example):
IF ([Jurisdiction] = 'Alabama' AND [CoCd] IN(1020,1047))
OR ([Jurisdiction] = 'Arkansas' AND [CoCd] IN(1020,1048))
OR ([Jurisdiction] = 'Georgia' AND [CoCd] IN(1048))
OR ([Jurisdiction] = 'Jurisdiction' AND [CoCd] IN(Values))
OR ([Filing Method] = 'Con')
THEN [App]
ELSE 0
ENDIF
If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.
Regards,
Jonathan
Hi @Hongli1216, below is the modified logic to suit the additional scenarios.
IF [Jurisdiction] = 'Alabama'
AND [CoCd] = 1020 or [CoCd] = 1047
THEN [App]
ELSEIF [Jurisdiction] = 'Arkansas'
AND [CoCd] = 1020 or [CoCd] = 1048
THEN [App]
ELSEIF [Jurisdiction] = 'Georgia'
AND [CoCd] = 1048
THEN [App]
ELSEIF [Jurisdiction] = 'Wisconsin'
AND [CoCd] = 1000
THEN [App]
ELSE 0
ENDIF
Hi Jonathan,
I like your formula very much! Thanks for your great help! There was some interesting things: in the front of your formula I had another formula, IF [CoCd]="1091" THEN [App] ELSEIF [Sep & Con] ="Con" THEN 0 ELSE [App] ENDIF, in order to make this formula work I had to choose V_String as CoCd's type but this type made your formula showing all App as Null; then I changed Int16 as CoCd's type which made your formula worked but my first formula showing Invalid type in operator==. I'm so confused about this. Please advise! Thanks for your time!