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

Alteryx designer Discussions

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

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.

Nebula
Nebula

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