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.
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)
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
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.