HI, How can Regex Replace be used to achieve the below thru Multi-Row formula tool- Problem and Expected result as below: ProblemNameValueIDN1Annual, Annual Annual1N1 2N1 3N2Annual, Quarterly, Monthly1N2 2N2 3 Expected ResultNameValueIDN1Annual1N1Annual2N1Annual3N2Annual1N2Quarterly2N2Monthly3

Regex Replace

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

Parse

Preparation

Accepted answers

DavidP

Her're an alternative to regex for this problem.

alternative to regex.png

alternative to regex.yxmd

MarqueeCrew

@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

Regex Replace.yxmd

All comments

DavidP

Her're an alternative to regex for this problem.

alternative to regex.png

alternative to regex.yxmd

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

@girisri,

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

Regex Replace.yxmd

girisri

@MarqueeCrew

Thanks! Nice way to solve it!

girisri

@DavidP

Thanks, This works too!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335