IIF statement with Filters

[STEPHAN555]

Hi,

I am trying to filter and retag a column with two filters on: 1 being engagement country being Switzerland, 2 account name is HSBC, Deutsche Bank, JPMorgan, BNP Paribas or Barclays. The field 'FSO WAM Filter' should be retagged to 'WAM Exceptions_Sw2'.

I have used the formula below but this doesn't seem to come through - anyone know what I'm doing wrong?

IIF ([EngagementCountry] = "Switzerland Geo"
AND
([AccountName] IN ("HSBC", "Deutsche Bank", "JPMorgan", "BNP Paribas", "Barclays"))
, "WAM Exceptions_Sw2" , [FSO WAM Filter] )

Thanks

Stephan

[jdunkerley79]

The expression looks fine. At a guess, there might be trailing white space. Try:

IIF (TRIM([EngagementCountry]) = "Switzerland Geo"
AND
TRIM([AccountName]) IN ("HSBC", "Deutsche Bank", "JPMorgan", "BNP Paribas", "Barclays")
, "WAM Exceptions_Sw2" , [FSO WAM Filter] )

[STEPHAN555]

This still didn't change anything in the output.. Do I need to add a filter function before making the changes?

Thanks!

[MarqueeCrew]

Can you copy some input records to a file that you want to find and include a few that you want to skip and then add them to the post please?

[STEPHAN555]

This is a sample of what the data in the input file looks like:

I would want to filter on Switzerland Engagement country and 'HSBC, JPMorgan...' on Account Name. Then retag the FSO WAM Filter to 'WAM Exceptions_Sw2'

AccountName	EngagementArea	EngagementCountry	EngagementRegion	FSO WAM Filter
Schroders	EMEIA	Germany Geo	FSO EMEIA	Other
Jpmorgan Chase & Co.	EMEIA	Germany Geo	FSO EMEIA	Other
Jpmorgan Chase & Co.	EMEIA	Germany Geo	FSO EMEIA	Other
Jpmorgan Chase & Co.	EMEIA	Germany Geo	FSO EMEIA	Other
HSBC	EMEIA	Switzerland Geo	FSO EMEIA	Other
Jpmorgan Chase & Co.	EMEIA	Switzerland Geo	FSO EMEIA	Other
HSBC	EMEIA	Switzerland Geo	FSO EMEIA	Other

[STEPHAN555]

In the output file, Alteryx is changing all the 'Switerzland Geo' engagement country entries for 'JPMorgan Chase & Co.' to 0 when I do the below formula. Any idea why? 

IIF([AccountName] = "Jpmorgan Chase & Co."
AND
[EngagementCountry] = "Switzerland Geo"  , [FSO WAM Filter] = "WAM_Exceptions_Sw2", [FSO WAM Filter])

[Syarifhidayat · Accepted solution]

Hi @STEPHAN555,

Here I attached some workflow.. I use formula tools to rename value inside FSO WAM Filter based on your condition.
But I want you to make sure, your condition should exactly same as your values. It is case sensitive.

Eg: your condition is 'JPmorgan', the tools will not change value at FSO WAM for Jpmorgan Chase & Co.

Please take a look at my workflow! Tell me if there some problem with my condition or etc.

[STEPHAN555]

Thank you! This worked - also just realised the 'output function' on the formula function wasn't assigned to 'FSO WAM Filter'. 

Thanks again!!