Unable to implement multi-Row formula in my scneario

[Jasdev]

Hi,

My scenario is in its basic terms:

if there are 4 rows as:

Jasdev

Jasdev Singh

Jasdev Singh Sachdeva

Jasdev Singh Sachdevaaaa

It should change all 4 of them to Jasdev Singh Sachdevaaaa. Even for the scenario

Logic I am trying to use for them is, if row 2 contains row 1 (complete string, if it is Jasdev R, it shouldnt work) then replace row 1 with row 2. The Expression in multi row formula I have used is created a new column checking and below:

IF CONTAINS([Row+1:Name],[Name])
THEN [Row+1:Name]
ELSE [Name]
ENDIF

Kindly provide some suggestions.

[JoshuaGostick]

Hi @Jasdev,

What kind of error or output are you getting?

One common error people have is that they forget to change the Data Type within the Multi-Row Formula to a string data type and to increase the Size so that all the text is displayed.

Does this solve your issue?

Thanks,

Josh

[OllieClarke]

Hi @Jasdev here's what I came up with:

[Jasdev]

Thank you for a quick response. I am not getting any error. I have changed the type to V_String with Size 50. I don't think that's the issue but the issue that I am facing is, it is not giving me desired output.

I am not exactly doing it with my name, that was just example but I was doing something else with some address field: The input is column 1 and output is column 2, basically it isnt doing anything just copying column 1 to column 2

1600 CLIFTON RD NE	1600 CLIFTON RD NE
1600 CLIFTON RD NE 24 ROOM 7123	1600 CLIFTON RD NE 24 ROOM 7123
1600 CLIFTON RD NE BLDG 1	1600 CLIFTON RD NE BLDG 1
1600 CLIFTON RD NE BLDG 18	1600 CLIFTON RD NE BLDG 18

[JoshuaGostick]

That's strange. I used your formula and it seemed to work on my side with your data. Perhaps you've clicked on a field in Group By (optional) by accident?

[Jasdev]

Thanks @joshua, yes I had it group by on that field. Shouldn't it be? I mean first it should group and only then check? Nevermind, I replaced it with sort and removed group by. Now it just appends only 1 row above, not all. Check this:

1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER DOCK 4
1 CNN CENTER DOCK 4	1 CNN CENTER DOCK 4
1 CNN CENTER NW	1 CNN CENTER NW FLOOR 10
1 CNN CENTER NW FLOOR 10	1 CNN CENTER NW FLOOR 10

[Jasdev]

My settings are attached in the screenshot:

[Jasdev]

Thanks for an alternate approach of not considering multi row formula. I tried using it with a huge production data sets and it looks like it takes forever to run the workflow. It gets stuck at the formula where the "group" variable is computed. 

[OllieClarke]

Hi @Jasdev I don't think that a multi-row formula is the way to go this time, as you've seen with your results, as long as you don't have too large a dataset, then the method I linked earlier gets you what you want. (here using the find and replace in Append, rather than replace mode). The drawback is that it joins every row onto every row, so with large datasets, will be slow.

edit: sorry, wrote this before I saw your response

[Jasdev]

Yes, it works for small scenarios. For big data it takes forever to run it. The solution that I wrote, it seems to be working fine but I found a small issue with it logically. If there are rows like 

1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER
1 CNN CENTER	1 CNN CENTER DOCK 4
1 CNN CENTER DOCK 4	1 CNN CENTER DOCK 4

In a way, it is working fine for what logic I have provided, it is changing the second last record based on last record. Now I kinda wanna do it again. So primarily I am looking for something that does it again and again in a loop till all of them are uniform. Not sure if that can be achieved or not.