Hello there, Is there a faster way to consolidate two data sets into one based on sales order number and sales order batch number (also identical to invoice number line description) Dataset A: Sales order number Sales order batch number customer name delivery P1000101ABCDenmark P1000102ABCUKP1000201XYZFinland Dataset B: Invoice number invoice number line description sales order number Invoice amount 90000101P10001EUR10090000202P10001EUR20090000301P10002EUR300

Hi @Miki2021 The FindReplace will only match a single field and not meet your requirements. The solution offered by @atcodedog05 @ https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Consolidate-data-using-join/m-p/841240/highlight/true#M207080 is optimal.

Hi @Miki2021 You can use join multiple tool which would give you full outer join and not leave out data. Workflow: Hope this helps : ) join.yxmd

Consolidate data using join

Miki2021

Hello there,

Is there a faster way to consolidate two data sets into one based on sales order number and sales order batch number (also identical to invoice number line description)

Dataset A:

Sales order number	Sales order batch number	customer name	delivery
P10001	01	ABC	Denmark
P10001	02	ABC	UK
P10002	01	XYZ	Finland

Dataset B:

Invoice number	invoice number line description	sales order number	Invoice amount
900001	01	P10001	EUR100
900002	02	P10001	EUR200
900003	01	P10002	EUR300

Workflow

Parse

Join

Accepted answers

All comments

atcodedog05

Hi @Miki2021

You can use join tool for this and join on keys.

Workflow:

Interactive lesson on join tool : https://community.alteryx.com/t5/Interactive-Lessons/Joining-Data/ta-p/76634

Hope this helps : )

join.yxmd

HomesickSurfer

Hi @Miki2021

EDIT: @atcodedog05 beat me to it...I concur with his solution.

Config the Join tool as follows:

MarqueeCrew

@Miki2021 ,

The join tool is the "fastest", but how you intend to use the data may lead to the discovery of ways to improve the speed of the join operation. In your sample data you have a relatively slim set of files. If you were not going to make use of a field (e.g. customer name), the JOIN tool allows you to de-select the field from output. To make the JOIN faster, you could add a SELECT tool BEFORE the JOIN tool. Remove any unwanted fields there and you will run faster. You wouldn't carry the groceries up 10 flights of stairs to decide which items were going to be left in your car.

Now that you are only carrying essential data with you, if there are data RECORDS that are unnecessary or can be summarized, you should take that action before the JOIN. Suppose you don't care about UK data, if you FILTER the data out ahead of the JOIN then it will run faster. If you have data that you can SUMMARIZE first, it is faster to summarize before the JOIN and then join after the summary.

Lastly, if you want to try using AMP then in your configuration you can use AMP and see if it speeds things up for you.

Cheers,

Mark

Miki2021

@atcodedog05

Thanks!

I also watched that video 🙂

Miki2021

@HomesickSurfer

thanks! The join tool will disregard those data that are not matched based on specific fields.

Is it better to use Replace/Find tool to append the data?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703