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 - Hoffice1) Booking ID: New2) Booking Ref: Should be 8 Digits and start with 93) 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 InformationHoffice BrandBooking IDStatusPrior My Fashion IDBooking RefMy Fashion ID NameMy Fashion ID ValueSourceNike NewConfirmedBNTHEE789MIK50:MFI000095897894569741T89745WOKNT122NDLMSJGHAN123456QOJAMSKHeadOfficeNike NewConfirmed BNTHEE789MIK50MFI00009589InternalNike NewConfirmedONTHEE789MIK50:MFI00001597147852369T89745WOKNX10ABDLMSJGHAN123456QOJAK8CHeadOfficeNike NewConfirmed ONTHEE789MIK50MFI00001597InternalNike Previous DayPendingONTHEE789MIK50:MFI0000159765412399T89745WOKNX10ABDLMSJGHAN123456QOJAK8CHeadOfficeNike Previous DayConfirmedREFHEE789MIK50:MFI0000100698574961T89745WOKNX10ABDLMSJGHAN123456QOJAK9EHeadOffice Data 2 Store Data StatusClient NameClient IDDetailsDormantJacob Shanne98745699JSD_98745699@Client Deal, London@Client, 1001.0@nike, 101258963333312@My Fashion ID, 1234@My Fashion User IDActiveMicke Green9231231212312312@Order Number, 101258963333313@My Fashion ID, MFI00001234@My Fashion User IDActiveJonathan9234567712345677@Order Number, 111258963223322@My Fashion ID, MFI00001597@My Fashion User IDActiveClaudie9512385285123852@Order Number, 111258969514131@My Fashion ID, MFI00008521@My Fashion User IDActiveTesse96457887TTA_56457887@Client Deal, London@Client, 1001.0@nike, 101258964444987@My Fashion ID, 1236@My Fashion User ID, MFI00005896@Prior My Fashion IDActiveJhonny Seacrest95475311JSA_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 IDStore IDTypeUpload789456974195475311ShoesY14785236992345677ShoesY 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 Join with Additonal Fields.xlsx

Join Tool and a Duplicate Function

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

Join with Additonal Fields.xlsx

Workflow

Datasets

Help

Join

Data Investigation

Accepted answers

Mathias_Nielsen

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

New Workflow5.yxmd

All comments

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

New Workflow2.yxmd

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

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

New Workflow5.yxmd

EmilAlteryx01

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703