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

Data stacking (trail balance)

spsanu_EY
7 - Meteor

Hi,

 

I am trying to prepare workflow which will consolidate all monthly trial balance numbers one after another:

Current format:

Please refer attached excel file (problem)

Solution: 

on first column we need All account type and then all month's numbers should be place on after another (every month number should be "Debt - credit")

Format should look like this:

picture.PNG

 

Would it be possible to automate this exercise through Alteryx, if we can;t automate this entirely then would it possible to create separate tabs for each month, I mean in sheet1 we should have First three column then in sheet 2 we have next three column and so on. In this way, I can create a workflow which will give me the desire result. 

 

Thanks in advance for your help. 

Thanks,

Satya

 

4 REPLIES 4
Philip
12 - Quasar

Is this the way you receive the data each month? Personally, I'd work out a way to get the data in a better format for easier processing. You're going to have issues because of the two-row header and the addition of new columns instead of new rows. 

 

If you're determined to make this work, I can create a workflow. It will be gnarly, it might be easily broken and I'd never guarantee the results (based on processing spreadsheets like this in the past), but I can create it. Just let me know.

 

Better would be to ask for a data source with columns of date, account, debit, credit. It will make your workflow a lot simpler, easier to maintain, and give you dependable outputs.

CharlieS
17 - Castor
17 - Castor

@spsanu_EY

 

I completely agree with @Philip, there's definitely opportunity for a better input state. It took a bit, but I've attached the "gnarly" solution.

spsanu_EY
7 - Meteor

Hi Philip,

 

Thanks for taking out time and looking into this problem.

 

Regarding the input file, it is not in our hand to get date in right format. We can only push our clients to sent data in excel format, rest we need to play around with Alteryx or excel to get the desire output. 

 

However, normally we receive trial balance in proper format format (separate tab for each month), this was one of the case and I am guessing we are going to receive more files like this, hence I was looking for proper solution through Alteryx.

 

Thanks & Regards,

Satya Prakash

spsanu_EY
7 - Meteor

Thanks Charlie for helping me out with the workflow... I was looking for same output...just to know, would it be possible to split each month trial balance into different excel tabs, I mean first three column will go in sheet1 then next three column will go in Sheet2 and so on.

 

Again thanks for your help.

 

Thanks & Regards,

Satya Prakash

Labels