How can I remove these multiples?

levi_kim · 2024-12-06T21:19:05+00:00

There was an error rendering this rich post.

Hello,

I have a Magic ETL that provides open order data based on an item number. One issue I am facing is if I have two or more types of open orders for an item, then they will cross multiply. For reference, I have 3 open internal requisitions and 3 open sales orders for 1 item. Instead of displaying 6 rows (3 IR and 3 SO), I get 9 rows and every combination possible. Is there anyway I can fix this in Magic ETL to display open orders in their own lines?

My table uses Item info and left joins on order datasets such as purchase orders, sales orders, work orders etc.

Find more posts tagged with

Magic ETL

Accepted answers

DavidChurchman

Append stacks any columns that match on top of each other. If your column names don't match, they won't stack on top of each other.

Table 1

Table 1 Item ID	Table 1 Order ID
A	1
B	2
C	3

Table 2

Table 2 Item ID	Table 2 Order ID
X	7
Y	8
Z	9

Table 1 appended to Table 2

Table 1 Item ID	Table 1 Order ID	Table 2 Item ID	Table 2 Order ID
A	1
B	2
C	3
		X	7
		Y	8
		Z	9

If you have columns that you want appending but aren't, make sure they have the same name before appending:

Table 1 Renamed

Renamed Item ID	Renamed Order ID
A	1
B	2
C	3

Table 2 Renamed

Renamed Item ID	Renamed Order ID
X	7
Y	8
Z	9

Renamed Table 1 appended with Table 2

Renamed Item ID	Renamed Order ID
A	1
B	2
C	3
X	7
Y	8
Z	9

If you want something more specific, you'll have to include more information about which columns are in each of your inputs and which you'd like in your outputs, and what you're seeing/not seeing when you attempt different configurations.

All comments

ColemenWilson

Could you append the data instead of left join?

levi_kim

Hi @ColemenWilson ,

Thanks for the quick reply. I will give it a shot but I don't think it's the best option due to the different structure in the datasets. Any other ideas?

DavidChurchman

You said you want 6 lines in the output. If you have IRs A, B, C and ISOs 1, 2, 3, what would a successful join look like to you?

Option A:

IR	ISO
A
B
C
	1
	2
	3

Option B:

Type	Code
IR	A
IR	B
IR	C
ISO	1
ISO	2
ISO	3

Option C:

Some other ID that ties the correct IR and ISO to the correct row?	IR	ISO
U	A	3
V	B	2
W	C	1
X	A	1
Y	B	2
Z	C	3

Options A and B make more sense to me and would use an append. Option C would use a join, but we need more information about how you're trying to join the two to know which combination of IR and ISO should go on each row. I arbitrarily chose two combinations to get to 6 rows (A3B2C1|A1B2C3), but I believe there are 6 possible combinations for a total of 18 rows (A1B2C3|A1B3C2|A2B1C3|A2B3C1|A3B1C2|A3B2C1)

levi_kim

Hi @DavidChurchman ,

Thanks for your response. Option A is what I'd like to see. I used the Append Rows tile in my Magic ETL but it's not working as expected. A lot of fields that should be populated were empty such as Item Number. For Append Rows tiles, I chose to include all columns. I think I might be missing a key aspect/understanding of the function. For reference, in my old ETL I left joined on Org ID and Item ID. Any ideas?

DavidChurchman

Append stacks any columns that match on top of each other. If your column names don't match, they won't stack on top of each other.

Table 1

Table 1 Item ID	Table 1 Order ID
A	1
B	2
C	3

Table 2

Table 2 Item ID	Table 2 Order ID
X	7
Y	8
Z	9

Table 1 appended to Table 2

Table 1 Item ID	Table 1 Order ID	Table 2 Item ID	Table 2 Order ID
A	1
B	2
C	3
		X	7
		Y	8
		Z	9

If you have columns that you want appending but aren't, make sure they have the same name before appending:

Table 1 Renamed

Renamed Item ID	Renamed Order ID
A	1
B	2
C	3

Table 2 Renamed

Renamed Item ID	Renamed Order ID
X	7
Y	8
Z	9

Renamed Table 1 appended with Table 2

Renamed Item ID	Renamed Order ID
A	1
B	2
C	3
X	7
Y	8
Z	9