This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
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: firstname.lastname@example.org 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.
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
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).
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.