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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Help with combining data and creating a field that is a date field

Meteoroid

Hello, I have the following data set with 2 sheets (see attached). I want to combine the two data sets with a join and union on project name, but then create 3 combined columns with the following fields: score, total, and date out of the columns I have for each project and name (MINIMIZING data points). I am trying to join based on project name, but also match base on the date column so that each month has an entry to minimize data points. Right now I get a separate entry for each score, total, etc. The data I want is shown in the picture below, and my current workflow looks like the below. (ignore the extra nodes, they are for data cleanup)Capture.JPG

Capture2.JPG

 

Meteoroid

Note that I know how to create a transpose as a pivot on the score and total fields, my biggest problem is matching based on the date field for those two score and total fields, minimizing my data.

Magnetar

Hi @mham

 

The trick here is to treat the 2 groups of columns in parallel and then join up the results based on a row id that you create after the transpose

Solution.png

Note that the Input takes all the .xlsx in the current directory.  That's why I have the mapping Text Input tool to translate file name to year.  If you load each file with a different input tool, then move the Date formula tool to just after you read and preparse each file and before you join the results of both files.

 

Dan

 

 

Labels