Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

IIF statement with Filters

Highlighted
Meteoroid

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

Highlighted

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] )
Highlighted
Meteoroid

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

 

Thanks!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Meteoroid

 

 

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'

 

AccountNameEngagementAreaEngagementCountryEngagementRegionFSO WAM Filter
SchrodersEMEIAGermany GeoFSO EMEIAOther
Jpmorgan Chase & Co.EMEIAGermany GeoFSO EMEIAOther
Jpmorgan Chase & Co.EMEIAGermany GeoFSO EMEIAOther
Jpmorgan Chase & Co.EMEIAGermany GeoFSO EMEIAOther
HSBCEMEIASwitzerland GeoFSO EMEIAOther
Jpmorgan Chase & Co.EMEIASwitzerland GeoFSO EMEIAOther
HSBCEMEIASwitzerland GeoFSO EMEIAOther
Highlighted
Meteoroid

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])

Alteryx Partner

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.

Highlighted
Meteoroid

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

 

Thanks again!!

Labels