Import data changes format

[aderdiger]

Hi all,

I need some help formatting my data in alteryx so that I can run multiple imports through the same workflow even when they are formatted differently.

Below are some examples of the different ways my import data may be presented. Does anyone know how I can get alteryx to just pick up the Account, Debit, and Credit information I need and ignore the other columns? I am looking for something dynamic that will pick up the correct information regardless of the import format. 

Any help would be greatly appreciated!

[MSalvage]

@aderdiger,

Seems to me if these are excel files you can just throw in a select tool after the input, only selecting those three columns and deselecting *Unknown. Alteryx should automatically find the first row with data in it and the select tool will deselect any column other than those three, after setting it up on one set of data.

Hope this helps,

MSalvage

[aderdiger]

I have tried select, but since the data starts on varying rows, the field
names do not correspond to the headings I want.

--
*Amanda B. Derdiger*
PwC / Asset Wealth Management
Email: amanda.b.derdiger@pwc.com
847-946-6474
PricewaterhouseCoopers LLP
Three Embarcadero Center, San Francisco, CA 94111
*http://www.pwc.com/us *

______________________________________________________________________
The content of this email is limited to the matters specifically addressed herein and is not intended to address other potential tax consequences or the potential application of tax penalties to this or any other matter.

______________________________________________________________________
The information transmitted, including any attachments, is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you received this in error, please contact the sender and delete the material from any computer. PricewaterhouseCoopers LLP is a Delaware limited liability partnership. This communication may come from PricewaterhouseCoopers LLP or one of its subsidiaries.

[MSalvage]

@aderdiger,

Hmmm double check, in your input tool, that you do not have First Row Contains Data checked.

With this unchecked Alteryx should go and find the first row with data in it and make that row your headers. Are the rows above the data always empty?

Best,

MSalvage

[aderdiger]

Yes there are headers and other text above the data I need. Sorry, my examples were not very clear.

This is a better example of what the import looks like in alteryx. However, the relevant data that I need starts in different rows and columns each time.

[Syarifhidayat]

Hi @aderdiger,

I do not know is it my workflow can help you or not. But you can take a look from it to get an idea. I will explain it little bit to make sure you understand what i'm doing.

1- I use Union atools to union all file. Then, I change the configuration from Auto Config by Name to Manually Configure Fields. Here, you arrange each column to make sure all account,credit and debit is located in the same column.

2- Use Select Tools to select only account, debit and credit column and don't forget to rename your fields and also to change the data type for column debit and credit (change it from string to double).

3- Use Filter Tools to pick the row that contains Account#

4- Use Data Cleansing Tools to change null value in column debit and credit to 0 value

Hope this workflow may help you.

[MSalvage]

@aderdiger,

I think I have a solution that is dynamic. It is a little ugly but i think it should work for you. It is a workflow and a macro. The workflow finds the row that contains 'account'(so if the column name is different you will have to add to it). The pushes the row number into the batch macro which pulls the same excel file in starting on the row the workflow found and pushes out the data from there down.  Macro and workflow attached. (you should not have to fiddle with the macro but you might want to check it out just to see how it works).

Hope this helps,

MSalvage

[KaneG · Accepted solution]

Remember here that you can keep it simple. I imagine that with these types of FieldNames, you're not going to often have more than a couple million rows and so this should work fast enough. (Not that speed is probably tour major concern).

RecordID, then transpose it all. Filter to find your Column Headers and then Summarize to get the Row Number of your headers and a column of the FieldNames they are in. Append that back to the transpose and filter for RecordID >=HeaderLine AND Fieldnames that are in the filedname column. Finally Tranpose and Dynamic Rename. It could easily be a macro as well.

Attached.

Kane

[aderdiger]

That worked! Thank you so much :)

[Rosh]

This helped solve my problem. Thanks