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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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