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

IIF statement with Filters

STEPHAN555
6 - 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

7 REPLIES 7
jdunkerley79
ACE Emeritus
ACE Emeritus

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
6 - Meteoroid

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

 

Thanks!

MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
STEPHAN555
6 - 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
STEPHAN555
6 - 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])

Syarifhidayat
8 - Asteroid

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
6 - 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