Multi Row and Multi Field

[VJ_88]

Hi,

I have to design a workflow to prepare my excel in desired format

Transformation needed

1. There are 102 columns if ` is found anywhere in any column any row replace with blank
2. similarly -` should be replace by blank
3. Now a more complex part for me as still trying to figure out 
   1. Column Name ending with _NF and _DF cannot be blank all blank values to be replaced by -1 and _DF with 1900
   2. There could be multiple column whose column name could end _DF 
   3. How can we apply formula on all such even if a new column is added in future ending with _NF or _DF 

[OllieClarke · Accepted solution]

@VJ_88 you can do all of this with the multi-field formula tool (or 2 tools).

The first tool would use 

REPLACE(REPLACE([_currentfield_],'-`',''),'`','')

with all fields (including dynamic or unknown fields) selected.

For the complex part, again tick all fields (including dynamic or unknown fields) and then use the following formula

IF IsEmpty([_currentfield_]) AND Endswith([_currentfieldname_],'_DF') THEN 1900
ELSEIF IsEmpty([_currentfield_]) AND Endswith([_currentfieldname_],'_NF') THEN -1
ELSE [_currentfield_]
ENDIF

 If these fields are strings, then you may need to do '1900' and '-1' rather than the unquoted versions I put above.

I'm away from Alteryx atm, so test that formula out, but I think it should work.

Ollie