Multi-Row Formula

[spencer046]

Hello, 

My objective was to replace the "Revised Name 2" for a particular location group with the highest frequency count name. First, I duplicated "Revised Name 2" to "Revised Name 3", then used Multi Row tool to update the Revised Name 3 column for the location group (record 145-153) using the expression [Row-1:Revised Name 3]. But I want to use the "Create New Field" option in Multi-Row Formula tool so that process would take place in Revised Name 2 column, but the output would be shown in the new column. 

When I am using the "Create New Field" option in Multi-Row Formula tool, it gives me below output:

I believe I haven't written the expression correctly. Need advise on how to resolve this with the correct expression that would give me the output like in the 1st picture. Thanks in advance.

[DavidP]

Hi @spencer046 

Here's another way of doing it.

With the sample tool, you can use the Group by function and select only the 1st row from each group, then join this to your dataset, take only Revised Name 2 from the Right input and rename it to Revised Name 3. Example attached.

[Qiu · Accepted solution]

@spencer046 
Your multiple Row Formula is only just copying the last row data, so not forming a looping.

I added some more dummy data to demostrate how will work.

Hope it helps and appreciate you would mark it as accept if it does.

[DavidP · Accepted solution]

You can also use the Group By functionality in the multi-row formula tool to treat each group as an individual dataset.

[spencer046]

@Qiu @DavidP Thanks a lot, both of them works!

[Qiu]

@spencer046 
Thank you also for the accept mark!

[spencer046]

@Qiu @DavidP coming back with another query:
For the below records, for this particular location key group,, the Revised Name 2 (records 234 through 237) are not different versions of "Miele" companies, rather they are completely different companies.

Is there a way, we can look for a similarities (maybe using the first word of the company names) within the Revised Name 2 column, that would replace the "Miele" company's different name versions with one name with the highest freq, and do the same for other patterns, or leave the company name as it is, in Revised Name 3 column, for this location key group?

Meaning in Revised Name 3 column, I would like to see "Miele International Company" replacing Revised Name 2 record 231-233, 238, and leaving others the same as it is unless it find any other unique similarity. 

Please let me know if my requirement appears as confusing 😞

[DavidP · Accepted solution]

Hi @spencer046 

How about something like this?

You can extract the 1st word from the company name as you suggested and then do a fuzzy match to generate a key that groups all similar names together. This key can then be used to group all records along with location key for the multi-row formula.

[spencer046]

@DavidP that works perfectly, thank you! I also did similar approach but instead used only the first word of the names to do further group by in the Multi row formula