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

Update fields by largest string counts

knozawa
11 - Bolide

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

4 REPLIES 4
Bob_Blackey
11 - Bolide

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

 

 

 

knozawa
11 - Bolide

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

knozawa
11 - Bolide

Hi Bob,

 

I think I figured it out!

Alteryx Sample.png

Thank you for your help.

 

Sincerely,

Kazumi

 

Bob_Blackey
11 - Bolide

Good job!

 

Bob

Labels