Hi all,
Hope someone can help with my question! I have a data set that currently looks something like this:
2000-01-01 | F2 | 2000-01-02 | F3 | 2000-01-03 | F5 |
Name 1 | Surname 1 | Name 4 | Surname 4 | Name 7 | Surname 7 |
Name 2 | Surname 2 | Name 5 | Surname 5 | Name 8 | Surname 8 |
Name 3 | Surname 3 | Name 6 | Surname 6 | Name 9 | Surname 9 |
And I am aiming to concatenate Name and Surname into the dated columns, as below:
2000-01-01 | 2000-01-02 | 2000-01-03 |
[Name 1] + " " + [Surname 1] | [Name 4] + " " + [Surname 4] | [Name 7] + " " + [Surname 7] |
[Name 2] + " " + [Surname 2] | [Name 5] + " " + [Surname 5] | [Name 8] + " " + [Surname 8] |
[Name 3] + " " + [Surname 3] | [Name 6] + " " + [Surname 6] | [Name 9] + " " + [Surname 9] |
Does anyone know how I could do this? Unfortunately, the dates change with each file so I can't manually concatenate based on field name. I was wondering if I could use a method that looks for all even numbered columns and concatenates to the previous column?
Any help would be great!
Solved! Go to Solution.
.
Kindly ignore, as I missed to see the heading will change dynamically
Hi @Ashman2304 here's a way to do it:
The multi-row looks for the hyphen in the date, so if your other column names have a hyphen then you could be more precise with the following formula (still grouped by recordID)
IF Regex_MAtch([Name],'\d{4}-\d{2}-\d{2}') THEN [Name] ELSE [row-1:name]
ENDIF
This uses RegEx to look for the date structure ####-##-##
Hope that helps,
Ollie
@FinnCharlton wrote:Hi @Ashman2304 , here is a way you can do it:
Thank you mate, looks so easy when you do it like this!