This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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@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 😞
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.
find max count from each group with multi-row with fuzzy match.yxmd