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

Alteryx designer Discussions

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

How to Concatenate Fields into one like Excel - QuickBooks example

Highlighted

I work at an accounting firm and we sometimes get source data (general ledger with all transactions listed by account) from client using QuickBooks. The data is exported like so: 

 

Account NameSub-Account Name Transaction details
1000 - Cash   
 1001 - RBC bank account  
   Transaction line item
   Transaction line item
   Transaction line item
 1002 - Scotiabank Transaction line item
   Transaction line item
   Transaction line item
   Transaction line item
1010 - Accounts Receivable  Transaction line item
   Transaction line item
   Transaction line item

 

For our purposes, we need to summarize transactions per account and the easiest way to do this is assign either the Account or Sub-Account name to the transactions, whichever the transaction is apart of most recently. 

 

To do this in Alteryx, I used the Multi-Row tool and put in the following formula (and create a new field. Keep in mind my workbook that I imported, imported blank cells as "" instead of as Null, if it were null, you would simply type !ISNULL([Account Name]) instead. Formula: 

 

If [Account Name]!="" then [Account Name] else
If [Sub-Account Name]!="" then [Sub-Account Name] else [Row-1:All Accounts]
endif endif

 

This makes the data look like this: 

All AccountsAccount NameSub-Account Name Transaction details
1000 - Cash1000 - Cash   
1001 - RBC bank account 1001 - RBC bank account  
1001 - RBC bank account   Transaction line item
1001 - RBC bank account   Transaction line item
1001 - RBC bank account   Transaction line item
1002 - Scotiabank 1002 - Scotiabank Transaction line item
1002 - Scotiabank   Transaction line item
1002 - Scotiabank   Transaction line item
1002 - Scotiabank   Transaction line item
1010 - Accounts Receivable1010 - Accounts Receivable  Transaction line item
1010 - Accounts Receivable   Transaction line item
1010 - Accounts Receivable   Transaction line item

 

Then we summarize using the summarize tool. Workflow attached. 

 

Hopefully this can help someone! Thanks

Alteryx
Alteryx

Hello Jesse,

 

I'm not entirely sure what you are trying to achieve.  I have replicated your table below by using simply adding in a data cleanse tool followed by your multi row formula tool.

 

I also produced another way to display the table which might be of use.

 

Let me know in more detail if this is not what you are trying to build.

 

 

Nick

Solution is in Version 11

 

 

Alteryx
Alteryx

Hello Jesse,

 

I'm not entirely sure what you are trying to achieve.  I have replicated your table below by using simply adding in a data cleanse tool followed by your multi row formula tool.

 

I also produced another way to display the table which might be of use.

 

Let me know in more detail if this is not what you are trying to build.

 

 

Nick

Solution is in Version 11

 

 

Hey Nick, 

 

Sorry - I attached the wrong formula in the file! Here's the updated one. Thanks for noticing - in my actual data I had "" as the blank cells, forgetting that Alteryx shows "Null". This should achieve the same result as your workflow. Also thanks for giving another way of showing it - this could be useful as well!

 

Jesse

Labels