Update fields by largest string counts

Hello,

I would like to update fields by the largest string counts. The sample data is following:

Name	Specialty	Specialty String Count
Apple	Allopathic & Osteopathic Physicians\|General Practice	52
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Hepatology	64
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology	75
Apple	Allopathic & Osteopathic Physicians\|Surgery	43

In this case, string count "75" is the largest number. Therefore, I would like to update Specialty field to "Allopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology".

Name	Specialty
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology

I was trying to use the Multi-Row formula. I could only update one row, but not all rows.

I would appreciate if there is any suggestions.

Sincerely,

Kazumi

Alteryx sample.xlsx

Tips and Tricks

Preparation

Accepted answers

knozawa

Hi Bob,

I think I figured it out!

Alteryx Sample.png

Thank you for your help.

Sincerely,

Kazumi

Speciality.yxzp

All comments

Bob_Blackey

Hi Kazumi,

I love the Multi_row formula tool but I didn't use it here.

I SORT the records so the highest count was the first record.

Then I used the SELECT RECORDS tool to get just the first record (because the S.R. tool doesn't get much love)

Then I APPEND FIELDS to add the specialty name of the longest string to each record

Then I use SELECT to drop the original specialty and rename the longest specialty

Cheers,

Bob

Speciality.yxmd

knozawa

Hi Bob,

Thank you for your reply. This is good when I have only one name. Sorry that I didn't tell you that I have more than one name. For example, I have 3 names: Apple, Banana, and Orange.

Name	Specialty	Specialty String Count
Apple	Allopathic & Osteopathic Physicians\|General Practice	52
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Hepatology	64
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology	75
Apple	Allopathic & Osteopathic Physicians\|Surgery	43
Banana	Allopathic & Osteopathic Physicians\|General Practice	52
Banana	Allopathic & Osteopathic Physicians\|Internal Medicine\|Hepatology	64
Orange	Allopathic & Osteopathic Physicians\|General Practice	52
Orange	Allopathic & Osteopathic Physicians\|Surgery	43

In this case, the desired output is following:

Name	Specialty
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology
Apple	Allopathic & Osteopathic Physicians\|Internal Medicine\|Transplant Hepatology
Banana	Allopathic & Osteopathic Physicians\|Internal Medicine\|Hepatology
Banana	Allopathic & Osteopathic Physicians\|Internal Medicine\|Hepatology
Orange	Allopathic & Osteopathic Physicians\|General Practice
Orange	Allopathic & Osteopathic Physicians\|General Practice

I think the select record tool does not work for this particular example. Is there any way to filter out the largest Specialty String Count per person so that I can append later on like you did?

Sincerely,

Kazumi

Alteryx sample.xlsx

knozawa

Hi Bob,

I think I figured it out!

Alteryx Sample.png

Thank you for your help.

Sincerely,

Kazumi

Speciality.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703