Fill in existing data by matching value from a column in another input

[Jimmylee4real]

hi all Alteryx Experts, 

I feel like this might be a common question which there had been loads of discussion going on but i was not able to find a solution to my specific use case after searching. 

I have an existing records after many formulas and tools on Alteryx but now I need to amend or fill in one single column to the existing records based on 3 criteria or matching values. 

I tried to use join the union or join function but the result is not as expected. 

For example: 

Current Records in Alteryx: 

Test Name	Test Systems	Test Date	Time (missing)	Recommendations (not relevant)
Tony	Photoshop	01 Mar		asdfasdf
Peter	Adobe	02 Mar		asffdsg
Johnny	Excel	03 Mar		gfhfghf
Mary	Python	04 Mar		sfgfsdfg

New Records: 

Test Name	Test Date	Time (want to append to original result)	Recommendations (not relevant)
Tony	01 Mar	15:00	asdfasdf
Peter	02 Mar	16:00	asffdsg
Johnny	03 Mar	19:00	gfhfghf
Mary	04 Mar	20:00	sfgfsdfg

Desired Results: 

Test Name	Test Systems	Test Date	Time (added here)	Recommendations (not relevant)
Tony	Photoshop	01 Mar	15:00	asdfasdf
Peter	Adobe	02 Mar	16:00	asffdsg
Johnny	Excel	03 Mar	19:00	gfhfghf
Mary	Python	04 Mar	20:00	sfgfsdfg

so basically i am trying to add the "column time" to existing records once the Test name, Test Date and Test systems are matching. thanks 

please advise! thank you Alteryx Community! 

[Emil_Kos]

Hi,

I have used join tool. Please let me know if this is the desired output. If not please give us a little bit more details 🙂

[paulfound]

Hi @Jimmylee4real 

I have used a join tool, and added a entry that hasn't done a test yet so you can see all entries at the end of the workflow.

[Jimmylee4real]

thanks for replying, Paul! 

based on the pic, you have used a join and union tool to sorta merge both files together? 

for join tool, may I know which columns  did you use to match?

i also want to raise out a pain-out: i actually only have 100+ records in existing file, but for the new file that contains "timing", there are 20k records and most of those records are irrelevant. thanks 

[paulfound]

Hi @Jimmylee4real 

I just joined on test name and test date.

I did include the worksheet on the original post, so you should be bale to see exactly what I did.

[Jimmylee4real]

for join tool, may I know which columns  did you use to match?

[paulfound]

[Jimmylee4real]

thanks Paul! this is what I want, but here comes another challenge. 

I try to append the time column based on a few unique criteria such as Test Name, Test Name and Test Systems, but unfortunately, the actual data is way more complicated than what it looks like in dummy data. There are tons of irrelevant and duplicated data. 

For the first part of data, i only have around 90+ records and some of those have null value on the time column so that's why I try to append the data from second part of data there. For some systems, the test name and time are generated by two different systems so that's why there are two different files. 

After running the join tool, most of the original data (which i must keep) are gone. can you please advise? thanks

[paulfound · Accepted solution]

Hi @Jimmylee4real 

There are 3 outputs from the join tool and here I union L & J together, this is essentially a Left Join in SQL.

If your records are missing they will be in L,J or R.

If this doesn't fix it maybe share a screen shot? If it does fix it please mark as complete.