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

Adding column with existing data

bcastle88
6 - Meteoroid

Hey Guys,

I'm having an issue trying to solve what would be a fairly easy fix in Excel. Our billing system assigns a single account number and name and then sub-accounts that can have distinct client names. I'm trying to create a 4th column where the account name is listed for all of the sub-accounts. Is there a tool or formula that could assist with this? Notice in the attached image each account number is also a Sub-Account

 

   New Column
AccountClient NameSubAccountParent Name
111000ABC Inc111000ABC Inc
111000BDC Inc111001ABC Inc
111000ABC LLC111002ABC Inc
111000ABC Co111003ABC Inc
222000Help Inc222000Help Inc
222000Help LLC222001Help Inc
222000Help Me222003Help Inc
222000Help Me Please222002Help Inc
333000Need Assistance333000Need Assistance
444000S.O.S444000S.O.S

 

6 REPLIES 6
Greg_Murray
12 - Quasar

I think this is what you're looking for. It uses a filter tool to find accounts and sub accounts that are the same and then assigns that account name to all of the matching accounts in the join. 

 Capture.JPG

cheers,

Greg

GarthMiles
7 - Meteor

would this be what you're looking for? (see attached)

 

Screen Shot 2018-07-27 at 10.26.40 AM.png

vishwa_0308
11 - Bolide

I would go with using mullti-row formula tool:

 

ParentName.JPG

GarthMiles
7 - Meteor

I'm not sure that would work, @vishwa_0308. Based on the sample data provided, there's no guarantee that [SubAccount] will match [Account] with an exact match function. In fact, the outcome will more often be a 'no match'.

Screen Shot 2018-07-28 at 1.10.38 PM.png

 

That being said, your IF-ELSE statement could be simplified by selecting [Account] as a 'Group By' option (i.e., no need for the 'elseif' clause)

 

summarizer
9 - Comet

This way works.  I would sort your data by Account and Sub Account ascending - since it seems your main account number and the first sub account (ending in all zeroes) is the Parent name you are wanting in your new column.  

 

Answer.jpg

Then Summarize it by grouping by the Account, and then choosing the first of the Account names (as you can see in the Summary configuration below).  

 

Answer2.jpg

 

Then join it up with your original data by the Account number.  Best of luck!!!

 

Answer 3.jpg

Answer 3.jpg

 

bcastle88
6 - Meteoroid

Thanks so much for the help everyone. I always find that many of the wrong or half solutions always end up assisting me with other problems i'm trying to solve.

 

This is such a  great community.

BC

Labels