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

Alteryx designer Discussions

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

Create new column based on field name

I am trying to clean up a PDF that I have converted. The problem in its conversion has been that empty columns have not been recognized as their own separate columns.

 

My data looks like this:

 

PurchasedSoldPurchased SoldPurchasedSold Purchased
1000250null1000250
null7002,700null700
nullnull3,800nullnull
500nullnull500null
nullnull3,800nullnull
null50nullnull50

 

I am trying to apply a function to separate columns based on their field names. The field names are either 'Purchased' or 'Sold' therefore, I would like to check the field name for either of those and then split the column based on this information.

 

The desired result is as follows:

 

PurchasedSoldPurchasedSoldPurchasedSoldPurchased
1000250nullnull1000250null
null7002,700nullnull700null
nullnull3,800nullnullnullnull
500nullnullnull500nullnull
nullnull3,800nullnullnullnull
null50nullnullnull50null

 

How can I go about doing this?

 

Thanks!

Alteryx
Alteryx

Something like this may work for you. I used a couple of text to column tools and a select tool to deselect the originals. 

 

pdf parse.png

Alteryx
Alteryx

@numberplugger You can also use the formula tool to edit the columns and create columns with the null() function!

 

Many ways to do this

Digan
Alteryx

I like the idea of using text to column, it gets the job done well for one column.

 

My only problem is I am trying to apply this to a large number of files therefore, I do not want to have to specify which column the text to column function is splitting.

 

Is there a way to tell Alteryx to go through each column and split it based on the same delimiter?

Alteryx
Alteryx

There is, but the issue in doing it dynamically is the column names that will ultimately end up as the same values. Is there another header row above that indicates what is being purchased or sold? It would make a lot of sense to include this in the purchased/sold headers so there will not be duplicates. 

Labels