Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-Row Formula

spencer046
8 - Asteroid

Hello, 

 

shahed_sheikh_0-1610399157178.png

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:

shahed_sheikh_0-1610382157566.png

 

shahed_sheikh_0-1610382038788.png

 

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.

 

8 REPLIES 8
DavidP
17 - Castor
17 - Castor

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.

 

DavidP_0-1610403277445.png

 

Qiu
20 - Arcturus
20 - Arcturus

@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.

0112-shahed_sheikh.PNG

DavidP
17 - Castor
17 - Castor

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

 

DavidP_0-1610454369223.png

 

spencer046
8 - Asteroid

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

Qiu
20 - Arcturus
20 - Arcturus

@spencer046 
Thank you also for the accept mark!

spencer046
8 - Asteroid

@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 😞

 

spencer046_0-1610580070051.png

 

DavidP
17 - Castor
17 - Castor

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.

 

DavidP_0-1610625933665.png

 

spencer046
8 - Asteroid

@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

Labels