This post is about the Join tool and the Join Multiple tool.
The easy way of Left Join using Multiple Join tool
There is sales data and products master data here.
Sales Data:
Products Master Data:
Generally speaking, when you want to do Left Join, you usually use the Join tool and Union tool.
To make this workflow, you have the assumption that there is the data to R output. In other words, you are assuming that there is some data from R input which is not matched with L input. But if all data from R input match the data from L input, you can use Join Multiple tool. That workflow is as follows.
The Join Multiple tool only does the Outer Join or Inner Join. The default setting of the Join Multiple tool is Outer Join. So, if some data from R input is unmatched, it output the unneeded data.
In this case, you have to filter using the Filter tool. The filter setting is that the key field of input #1 is null.
But actually, if you have to use the Filter tool, I feel the original way is fine. So I compared the two ways in terms of speed. The result is as follows.
Running time: Join Multitle tool ＞ Join + Union ＞ Join Multiple + Filter
* The left way is slow.
As a result, if there is unmatch data from R input, you have to use the Filter tool. So it is better using the Join tool and the Union tool. I recommend the way which I introduced at this post should to be used under the limited situation.
* I did this validation with AMP Engine on(engine compatibility mode is disabled).
I’m surprised at that the Filter tool uses much more time than I would have thought.
Note that I only did the simple validation. If you have any concern, please validate using your real data.
Sample Workflow Download
Contents of the next blog
The next post will be about Browse tool.
コメント