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

Alteryx designer Discussions

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

Vertical data import

Highlighted

Hi Alteryx designers!

 

I have a problem with a dataset I am working with. At this point I have an Excel document with various sheets in the same format. The only difference is the amount of sub entities, some entities have 1 sub or some have for example 5. Every sheet has a format that looks like the example as shown below:

Entity Name A  
 Sub entity Asub entity B

Balance

12

P&L 

34

Shares

21
Dividends12

 

After joining the sheets, importing and cleaning the data, the dataset looked like this: 

 

RecordIDEntity NameAccountnumberF3F4
1a Sub ASub B
2a

Balance

12
3aP&L34
4aShares21
5aDividend12
1a Sub C 
2bBalance2 
3bP&L3 
4bShares1 
5bDividends4 
1c Sub DSub E
2cBalance24
3cetc....23

 

Alteryx added the RecordID. Every new sheet starts with 1. 

 

The output I would like to have is as follows:

 

Entity Name AABCCC
  Sub ASub BSub CSub DSub ESub F

Balance

 123Etc..  

P&L 

 342   
Shares 211   
Dividends 124   

 

Unfortunately, when using the wildcard input macro, the data is imported vertically. So, the sheets are imported under each other instead of next to each other. What I would like to do is whenever a new entity starts on a new row, I want to move the data from the specific rows to new columns. 

 

The solution is probably transposing the data. However, I haven't found the correct way to do this. I hope you are able to help me!

 

If you need more information let me know.

Thanks you in advance. 

Elise

Magnetar
Magnetar

Hi,


I've attached a workflow that can accomplish this, although there may be simpler ways to do so.  To accomplish this I had to do some transposing of data, and then use a few cross tabs to create the values you were looking for, and then union'ed the data back together.

 

Hope this helps!

Alteryx
Alteryx

@Elisevandergeest

If you want to be able to just add the files to a folder, and have the workflow read in all the files within that folder, then Dynamically build out the report with a single button push; I would suggest this method.  The attached Workflow uses the Directory tool to import the paths of all of your files you add to the folder.  Then the "Directory Consolidation" macro I threw together assembles them into one file, without you having to do this manually.  This will work, regardless of the number of Sub Columns.  Then the rest of the workflow will Dynamically generate your report, so when you add new data files to the folder, all you have to do is click the Run Button.  The report that the attached workflow generates isn't exactly a match to the one you showed, so you may or may not want to make an edit, but setting your workflow like this will take a lot of manual work out of your process.

Directory Parse Macro.jpg

Alteryx
Alteryx

The attached file contains the macro and the data files.

Hi @ShawnM,

 

Thank you for your solution! Especially the crosstabs were a challenge, but it worked. I only had to manually make sure the rows were in the right order, but that's it. 

 

With kind regards,

Elise

Labels