Creating a Workflow for Excel transformations

ethankutch

Hello,

I am a beginner with Alteryx and am trying to create a workflow to automate transformations that would normally be done in Excel. 

The transformations steps are as follows:

1. Remove Null rows

2. Populate column A with Vendor name so that every bill/payment has a vendor name associated

3. Lift and shift all Bill lines up until the next bill payment

4. Create a new column called External ID, which is Vendor Name & Date & Amount

5. Copy down all Bill payment data for all bill transaction lines

6. remove unwanted columns and adjust to fit import templates

7. Assign all lines to cash account 1001

Specifically, I am looking for help on how to perform steps 2-4 in Alteryx. 

I have attached Example Data.xlsx, this file has the transformation steps laid out on one sheet, the example data in another, and finally what the data would look like after being transformed in Excel. All data is sample data.

Thanks!

Example Data.xlsx

acarter881

Hello, @ethankutch.

For #2, I believe a Multi-Row Formula tool will work. Perhaps if your data are blank (probably a [Null] value in Alteryx), then take the value from the previous row in Vendor, otherwise keep the current row's value.

For #3, I'm not sure what is meant by lift and shift lines.

For #4, creating a new column can be done with the Formula tool. You can concatenate the Vendor Name with the Date and the Amount. You may need to do a conversion (such as ToString for converting a number to a string).