Find, Append Formula Loop

[Vizblend]

Hi,

I'm creating a formula that will find a string in Field_1. If that criteria is met I would like it to take the corresponding value in Field_3 of the same row and then apply that data to every newly created field from the formula tool following it. The header content repeats so a control loop would need to then change to the newly found String2 and apply in to the next section of data.

I believe the approach of nested function statements can achieve this and have put an example approach below. The Formula may contain numbers or dates in the output, but would like to always treat as a V_String.

The Fornula does not throw any errors and jumps straight to the else clause. Any recommendations on how to better declare finding a value, return x in a new field in the same row, and outputting to y? I am working within a FLAT file. Example structure below.

Formula Tool -> Output Field = "New Field"

IF CONTAINS([FIELD_1], "THISTEXT") THEN REPLACE("", "", SUBSTRING(CORRESPONDING_FIELD_3, 1, 10)) ELSE "Did not work" ENDIF

Report Title
Report Date
Report Time

Cell 1 Cell 2 Cell 3 Cell 4
Cell 1 Cell 2 Cell 3 Cell 4
Cell 1 Cell 2 Cell 3 Cell 4

Report Title
Report Date

Cell 1 Cell 2 Cell 3 Cell 4
Cell 1 Cell 2 Cell 3 Cell 4
Cell 1 Cell 2 Cell 3 Cell 4

[s_pichaipillai]

Can you try the find and replace tool

see the sample attached

[s_pichaipillai]

if it does not help, could you please post some sample data , so that i can help you 

[Vizblend]

What I'm trying to achieve is posted above. I think the standard Find and Replace tool will not work in this case.

For example, lets say I want to take the content after the ':' delimiter in Report Title & Report Time and append that to all of the following data. The headers can appear multiple times so the appending would need to capture the new header it has now reached. Example below.

Report Title: TestFile
Report Date: 01/01/2016
Report Time: 12:00:00

Cell 1 Cell 2 Cell 3 Cell 4 TestFile 12:00:00
Cell 1 Cell 2 Cell 3 Cell 4 TestFile 12:00:00
Cell 1 Cell 2 Cell 3 Cell 4 TestFile 12:00:00

Report Title: TestFile2
Report Date: 5:00:00

Cell 1 Cell 2 Cell 3 Cell 4 TestFile2 5:00:00
Cell 1 Cell 2 Cell 3 Cell 4 TestFile2 5:00:00
Cell 1 Cell 2 Cell 3 Cell 4 TestFile2 5:00:00

[s_pichaipillai · Accepted solution]

Vizblend

try this one

not sure i grabed your data correctly , but this workflow will give you an idea to go forward

[Vizblend]

Thank you! It did lead me to the optimal solution.