Hello Everyone-
I have following Data Set :
<V1.0> A1-V |
|
Comments – 13 | “Regular – 1/16/2019” |
Comments – 14 | “1110N” |
Comments – 13 | “Regular – 1/16/2019” |
Comments – 14 | “1110N-2” |
Comments – 13 | “Regular – 1/16/2019” |
Comments – 14 | “1111N-4” |
records continue …… |
|
<V2.0> B1-V |
|
Comments – 13 | “Regular – 1/18/2019” |
Comments – 14 | “2220N” |
Comments – 13 | “Regular – 1/19/2019” |
Comments – 14 | “212N-1” |
records continue …… |
|
<V3.0> C1-V |
|
Comments – 13 | “Regular – 1/16/2019” |
Comments – 14 | “3330N” |
records continue …… |
|
Desired output: please note that there are several records between <V1> , <V2> and we need to iterate the Comments-13 and Comments-14 rows to get merged in one row, please see the desired output table below.
<V1.0> A1-V | “Regular – 1/16/2019” “1110N” |
New Field Name | “Regular – 1/16/2019” “1110N-2” |
New Field Name | “Regular – 1/16/2019” “1111N-4” |
New Field Name |
|
<V1.0> B1-V |
|
New Field Name | “Regular – 1/18/2019” “2220N” |
New Field Name | “Regular – 1/19/2019” “212N-1” |
<V1.0> C1-V |
|
New Field Name | “Regular – 1/16/2019” “3330N” |
I tried to add a Summarize tool at the end and did “Concat” to pull all records in one row and then slice it using “Text to Columns” using “<>” as delimiter . Logic doesn't seems to work ,as I couldn’t see all the records. Will really appreciate any help.
Thank you very much.
Solved! Go to Solution.
Hi @MD2050, here is what I came up with. I had to use a few different tools and logic, but ultimately, I think I came up with what you are looking for.
Thank you @JoBen -
Your solution worked perfectly.
Can you please help elaborate what was the purpose of "$1" ? by using MOD() you are trying to check if 13 is even or odd and based on that you are incrementing the count of RecordID .Is that correct?
IIf ( Mod ( ToNumber ( Regex_Replace ( [Right_Test],".+?(\d+)", "$1" ) ),2 )=1,[Row-1:RecordID]+1, [Row-1:RecordID] )
Thank you very much.
Okay. Glad it worked for you! The "$1" in a Regex formula is basically separating out the first marked section of an expression (anything in parenthesizes). So in this case, "$1" = "\d+", which is an unspecified amount of digits together. For the Mod(), you are correct. I was trying to find out whether the number produced was odd or even, and then basing the RecordID field on the result.