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 |
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 : )
Hi @Miki2021
EDIT: @atcodedog05 beat me to it...I concur with his solution.
Config the Join tool as follows:
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
_
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?
Hi @Miki2021
You can use join multiple tool which would give you full outer join and not leave out data.
Workflow:
Hope this helps : )
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... is optimal.