community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Update fields by largest string counts

Fireball

Hello,

 

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

 

NameSpecialtySpecialty String Count
AppleAllopathic & Osteopathic Physicians|General Practice52
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Hepatology64
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology75
AppleAllopathic & Osteopathic Physicians|Surgery43

 

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

NameSpecialty
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
AppleAllopathic & 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 Partner

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 

 

speciality.png

 

Cheers,

Bob

 

 

 

Fireball

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.

NameSpecialtySpecialty String Count
AppleAllopathic & Osteopathic Physicians|General Practice52
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Hepatology64
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology75
AppleAllopathic & Osteopathic Physicians|Surgery43
BananaAllopathic & Osteopathic Physicians|General Practice52
BananaAllopathic & Osteopathic Physicians|Internal Medicine|Hepatology64
OrangeAllopathic & Osteopathic Physicians|General Practice52
OrangeAllopathic & Osteopathic Physicians|Surgery43

 

In this case, the desired output is following:

NameSpecialty
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
AppleAllopathic & Osteopathic Physicians|Internal Medicine|Transplant Hepatology
BananaAllopathic & Osteopathic Physicians|Internal Medicine|Hepatology
BananaAllopathic & Osteopathic Physicians|Internal Medicine|Hepatology
OrangeAllopathic & Osteopathic Physicians|General Practice
OrangeAllopathic & 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

Fireball

Hi Bob,

 

I think I figured it out!

Alteryx Sample.png

Thank you for your help.

 

Sincerely,

Kazumi

 

Alteryx Partner

Good job!

 

Bob

Labels