Fill in blank from duplicated fields

[knozawa]

Hello,

I have an input that contains "duplicated" fields.

Using a dynamic rename tool to replace desired field names. 

"duplicated" fields were renamed to [column1]+digit.

Here is the desired output. 

I would like to dynamically fill in blanks.  I've tried to use "multi field formula" tool and "multi row formula" tool, but could not successfully achieve desired output.

I've attached a sample workflow.

Sincerely,

knozawa

[JosephSerpis]

Hi @knozawa I mocked up a workflow that I believe answers your question let me know what you think?

[knozawa]

@JosephSerpis ,

Thank you for your suggestion.  It works fine for fields that do not contain integer.  I apologize that I did not specify that sometimes there are integers in the fields (i.e. "past 5 years").

If possible, I would like to look for fields [column 1]+ 'digit' and fill in blank using those fields.

I've attached the updated input using your workflow.

Sincerely,

knozawa

[JosephSerpis]

Hi @knozawa can't you filter out those columns? I updated the workflow and just filtered out the past 5 years columns. 

[Jonathan-Sherman · Accepted solution]

Hi @knozawa,

I've made a slight ammendment to the piece of regex and dynamically fix changes in column headers after the cross-tab:

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed,

Regards,

Jonathan

[JosephSerpis]

Hi @knozawa I amended the workflow let me know what you think?

[Jonathan-Sherman]

@JosephSerpis i'm not sure you can't guarantee all underscores created by the cross-tab tool are spaces? What if there are other special characters in the column header that also are converted to underscores? That's why i went down the route of the field info tool to dynamically rename columns in my example

[knozawa]

Thank you, @Jonathan-Sherman  and @JosephSerpis .

I chose @Jonathan-Sherman 's workflow as a solution because it dynamically updates field names by using both "field info" and "dynamic rename" tool.  Also, yes indeed sometimes I have underscores in the field names.

Sincerely,

knozawa