Join Tool and a Duplicate Function

[EmilAlteryx01]

I would like to update the "Booking Ref" (Hoffice ID) in Data 2 with the corresponding "Client ID" (Store ID) from Data 1 under the following conditions related to Hoffice:

Conditions - Hoffice

1) Booking ID: New

2) Booking Ref: Should be 8 Digits and start with 9

3) The common fileds are Prior My Fashion ID: My Fashion ID Name: My Fashion ID Value in the "Details" column of Data 2 i,e the Store Information

Hoffice
Brand	Booking ID	Status	Prior My Fashion ID	Booking Ref	My Fashion ID Name	My Fashion ID Value	Source
Nike	New	Confirmed	BNTHEE789MIK50:MFI00009589	7894569741	T89745WOKNT12	2NDLMSJGHAN123456QOJAMSK	HeadOffice
Nike	New	Confirmed			BNTHEE789MIK50	MFI00009589	Internal
Nike	New	Confirmed	ONTHEE789MIK50:MFI00001597	147852369	T89745WOKNX10	ABDLMSJGHAN123456QOJAK8C	HeadOffice
Nike	New	Confirmed			ONTHEE789MIK50	MFI00001597	Internal
Nike	Previous Day	Pending	ONTHEE789MIK50:MFI00001597	65412399	T89745WOKNX10	ABDLMSJGHAN123456QOJAK8C	HeadOffice
Nike	Previous Day	Confirmed	REFHEE789MIK50:MFI00001006	98574961	T89745WOKNX10	ABDLMSJGHAN123456QOJAK9E	HeadOffice

Data 2

Store Data
Status	Client Name	Client ID	Details
Dormant	Jacob Shanne	98745699	JSD_98745699@Client Deal, London@Client, 1001.0@nike, 101258963333312@My Fashion ID, 1234@My Fashion User ID
Active	Micke Green	92312312	12312312@Order Number, 101258963333313@My Fashion ID, MFI00001234@My Fashion User ID
Active	Jonathan	92345677	12345677@Order Number, 111258963223322@My Fashion ID, MFI00001597@My Fashion User ID
Active	Claudie	95123852	85123852@Order Number, 111258969514131@My Fashion ID, MFI00008521@My Fashion User ID
Active	Tesse	96457887	TTA_56457887@Client Deal, London@Client, 1001.0@nike, 101258964444987@My Fashion ID, 1236@My Fashion User ID, MFI00005896@Prior My Fashion ID
Active	Jhonny Seacrest	95475311	JSA_65475311@Client Deal, London@Client, 99.0@nike, 101258965555987@My Fashion ID, 8523@My Fashion User ID, MFI00009589@Prior My Fashion ID

The final output should replace the "Booking Ref" (Hoffice ID) from Data 2 with the "Client ID" (Store ID)  from Data 1

Hoffice ID	Store ID	Type	Upload
7894569741	95475311	Shoes	Y
147852369	92345677	Shoes	Y

The HOffice _Head Office Data comes in every hour - 

The workflow will be run every hour - to ensure each HOffice ID is correctly matched with its corresponding Store ID. How can I prevent duplication, ensuring each HOffice ID is only considered once in that day? is there a workflow check 

[Mathias_Nielsen]

Hi,

I got this far, but there is no record with booking ref starting with 9 and only 8 digits long that can be joined with the other data. Maybe you can try to make this work. I gave it a shot :D

[EmilAlteryx01]

This is exactly what I was looking for. I'm currently reviewing the data I provided, which was limited to certain line items.

I have one final question: Can we use the 'Join' Tool to look up more than one column?

The left has"Prior My Fashion ID"  - is it possible to look up 2 columns on the right - i.e, My_Fashion_User_ID and Prior_my_Fashion_ID

[Mathias_Nielsen]

The workflow actually already does this. I duplicate any orderline on the right that has a Prior_My_Fashion_ID.

The two last tools of the right join combines My_Fashion_User_ID and and Prior_my_Fashion_ID into a single cell (My_Fashion_User_ID). Then i split the row so that there are two records with each of the ID's. Try and look at the data i input 6 records but at the end of this there is 8 two of them are identintical except for the My_Fashion_User_ID with two other lines

[Mathias_Nielsen · Accepted solution]

You can also do this, It should yield the same result

[EmilAlteryx01]

HI Mathias- Workflow 5 is perfect - Thank you soo much