Regex Replace

[girisri]

HI,

How can Regex Replace be used to achieve the below thru Multi-Row formula tool- Problem and Expected result as below:

Problem
Name	Value	ID
N1	Annual, Annual Annual	1
N1		2
N1		3
N2	Annual, Quarterly, Monthly	1
N2		2
N2		3
Expected Result
Name	Value	ID
N1	Annual	1
N1	Annual	2
N1	Annual	3
N2	Annual	1
N2	Quarterly	2
N2	Monthly	3

[DavidP · Accepted solution]

Her're an alternative to regex for this problem.

[Kenda]

Hey @girisri

If you're really wanting to use Reg_Ex and your data is always in the format shown in your example (with three rows per name and all of the values listed in the first row of the value field separated by commas), you could do it like this:

Add a Multi-Row Formula tool to edit the Value field with this expression:

trim(iif([ID]=2,regex_replace([Row-1:Value],"(.*)\,(.*)\,(.*)","$2"),iif([ID]=3,regex_replace([Row-2:Value],"(.*)\,(.*)\,(.*)","$3"),[Value])))

Then add a normal Formula tool to edit the Value field again with this expression:

trim(IIF([ID]=1, REGEX_Replace([Value], "(.*)\,(.*)\,(.*)","$1"), [Value]))

Note I had to add a comma for the N1 Value field as your sample data did not have that. As I said, this will work so long as your real data has the same form as your sample. 

If you have a dynamic number of rows, however, I would recommend approaching this like @DavidP suggested with using the Text To Columns tool first, Filtering out the empty values then modifying the ID field. If you want to still use the Multi-Row Formula tool here instead of the Tiles, you could group by the Name field and modify the ID field with this expression:

[Row-1:ID]+1

Hope this helps!

[MarqueeCrew · Accepted solution]

@girisri,

While @Kenda has used a regular expression, I wouldn't use one for this requirement.  The pattern that I see is that the ID indicates which word to use.  For that reason I would use the GetWord() function instead.  GetWord finds the zero-based word number.  In other words, Word 1 is Word 0.

My approach was to first find the MAX (Value and ID) then create the required number of rows.  Then I calculate the word for the value and rename fields.

Cheers,

Mark

[girisri]

@MarqueeCrew 

Thanks! Nice way to solve it!

[girisri]

@DavidP

Thanks, This works too!