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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

Gather all 9 clues to complete the final Weekly Challenge on Dec 16!

Learn More
SOLVED

How to join data and have multiple results?

Meteoroid

I have a list of journal entries on one side and I have a list accounts associated with each entry on the other. Is there a way to use the join tool to combine the accounts of each entry?

 

Ex:

List 1

JE00011100: Revenue
JE00011200: Cash
JE00014000: Accounts Receivable

JE0005

XX
JE0005XX

 

List 2

JE0001
JE0002
JE0003

 

Desired End Result

JE0001 1100: Revenue, 1200: Cash, 4000: Accounts Receivable
ACE Emeritus
ACE Emeritus

Hi @easher003,

 

Do the join as usual, on the first column.  The middle output will have three rows, all for JE001. Then do a Cross Tab to concatenate the multiple rows into a single row. (See attachment for this done on your sample data).

 

Hope that helps!

John

Meteoroid

Thanks for the reply!

 

I used your advice but the crosstab output something along the lines of the following...

 

JE Number

JE002JE003JE004JE005
JE002Revenue   
JE003 Revenue  
JE004  Revenue, Accounts Receivable 
JE005   Accounts Receivable

 

Is there a way to only have one column looking something like...

 

JE NumberAccounts
JE001Revenue
JE002Revenue
JE003Revenue, Accounts Receivable
JE004Accounts Receivable
ACE Emeritus
ACE Emeritus

Understood, this can be done by adding a generic field label and using that as the Field Header in the Cross-Tab.  Please see the attached for the actual workflow.

Asteroid

You can also achieve this by just using the summarize tool to streamline things. Just do a group by for the JE number and a concatenate for the description. You can change the default seperator to a comma with space (from a comma) for visual clarity.

ACE Emeritus
ACE Emeritus

@echuong is absolutely spot on - recommend adding his solution to "Accepted."

 

 - John

Labels