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

Formula_Put Number Under Particular Entity

Hongli1216
8 - Asteroid

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!

 

 

 

 

7 REPLIES 7
fmvizcaino
17 - Castor
17 - Castor

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

 

AbhilashR
15 - Aurora
15 - Aurora

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

AbhilashR_0-1599686069634.png

 

 

Hongli1216
8 - Asteroid

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!

Hongli1216
8 - Asteroid

Hi AbhilashR,

 

Thanks for your quick response! Actually there is two different filing method attached with more than two states. When we write formula for Sep filing but we still want to keep Con filing's app. Please advise! Appreciated your time!

Jonathan-Sherman
15 - Aurora
15 - Aurora

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

AbhilashR
15 - Aurora
15 - Aurora

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

 

Hongli1216
8 - Asteroid

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!

Labels