Multi-Field formula with Concatenated string for [_CurrentFieldName_]
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, I am trying to set certain fields NULL depending on what the fieldnames are, from a specific list.
So the non-dynamic version in the multi-field formula tool is:
IF [_CurrentFieldName_] IN ("FieldNameA","FieldNameB","FieldNameC")
THEN NULL()
ELSE [_CurrentField_]
ENDIF
Which works fine.
But I would like to make it dynamic, so I make a new field containing the relevant fieldnames concatenated with quotes and commaseparated:
ConcatVariable = "FieldNameA","FieldNameB","FieldNameC"
But when I then change the syntax in the multi-field formula tool, it doesnt work anymore:
IF [_CurrentFieldName_] IN ([ConcatVariable])
THEN NULL()
ELSE [_CurrentField_]
ENDIF
I have of course Googled and searched, but in vain :( Any tips on why this doesnt work, or alternative solutions?
Solved! Go to Solution.
- Labels:
- Expression
- Preparation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@MartinJacobsen I think you would need to switch to a contains formula. Here would be something simple:
If contains([ConcatVariable],[_CurrentFieldName_]) then Null() else [_CurrentField_] endif
Note that if you had fields Field1 and Field11 and you wanted to Null Field11, it would null both Field1 and Field11. If you want to avoid an issue like this, then you could just add the quotes around your fieldname.
If contains([ConcatVariable],'"'+[_CurrentFieldName_]+'"') then Null() else [_CurrentField_] endif
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Fantastic, I implemented your second solution and it worked flawlessly! Thank you very much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
👍 For what it's worth, Alteryx reads the field name list as 3 separate items, and it compares the currentfieldname name to each:
IF [_CurrentFieldName_] IN ("FieldNameA","FieldNameB","FieldNameC")
When you change it to a formula, it unfortunately is seen as a single string (not 3 separate items)
IF [_CurrentFieldName_] IN ([ConcatField])
where [ConcatField] = '"FieldNameA","FieldNameB","FieldNameC"'
