In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

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

Multi Row and Multi Field

VJ_88
8 - Asteroid

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 
1 REPLY 1
OllieClarke
15 - Aurora
15 - Aurora

@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

Labels
Top Solution Authors