Need help in multiple join

[MadhavTR]

Hi All,

I am facing issue in using multiple joins. I have data coming in 3 streams

Stream 1	Stream 2	Stream 3
Reference	Reference	Reference
compcode	compcode	compcode
gstcode	gstcode	compname
Amount	Tax	ABN
compname	compname
transdate

I am using multiple join on Reference, compcode but not getting right results. What am i doing something wrong?

Let me know how can i give more info if needed.

[DavidSkaife]

Hi @MadhavTR 

You've not specified why the output is wrong, but, by default the Join Multiple tool will output all rows from the incoming data, regardless of them joining or not - take a look at the tool example for further info on this

There is a box to tick to specify that you only want records that join from all inputs, otherwise please provide more detail on the output you're getting and the expected output.

[MadhavTR]

On a different note how to use the joins to merge the data from stream 1 and stream 2.

For example

Stream 1

Reference	compcode	gstcode	Amount	Compname
12345	401	P3	100	abc
12345	401	P1	200	abc
12345	199	P1	100	xyz

Stream 2

Reference	compcode	gstcode	Tax	Compcode
12345	401	P3	10	abc

Expected Output

Reference	compcode	gstcode	Amount	Compname	Tax
12345	401	P3	100	abc	10
12345	401	P1	200	abc	0
12345	199	P1	100	xyz	0

I have a third stream too but i am stuck in this only.

[DavidSkaife · Accepted solution]

Hi @MadhavTR 

Use a normal Join tool, joining on Reference, Compcode, GSTcode and compname then add a Union tool after the output to bring in the unjoined records

[MadhavTR]

Thanks a lot. It helped.