how to do a union with a condition

Question

we are working on alteryx :

having this table A :

COL1 COL2 COL3 c10 c11 ID
OUT ABCD 7000 XYZ3 XYZ33000 A
OUT ABCD 5000 XYZ1 XYZ11000 A
OUT ABCD 8000 XYZ5 XYZ55000 A
OUT ABCD 5000 XYZ1 XYZ111000 B

and this table B :

ID COL4 COL5 COL6
A Product A NOOF REF001
A Product C NOOF REF003
A Product D NOOF REF004
A Product E NOOF REF005
A Product A NO REF001
A Product B NO REF002
A Product C NO REF003
A Product D NO REF004
A Product E NO REF005
A Product F NO REF006
A Product G NO REF007
C Product B NOOF REF002
B Product H NO REF008

we want the result to be like this :

ID COL4 COL5 COL6 COL1 COL2 COL3 c10 c11
A Product A NOOF REF001 OUT ABCD 7000 XYZ3 XYZ33000
A Product C NOOF REF003 OUT ABCD 5000 XYZ1 XYZ11000
A Product D NOOF REF004 OUT ABCD 8000 XYZ5 XYZ55000
A Product E NOOF REF005 OUT ABCD 8000 XYZ5 XYZ55000
A Product A NO REF001 OUT ABCD 8000 XYZ5 XYZ55000
A Product B NO REF002 OUT ABCD 8000 XYZ5 XYZ55000
A Product C NO REF003 OUT ABCD 8000 XYZ5 XYZ55000
A Product D NO REF004 OUT ABCD 8000 XYZ5 XYZ55000
A Product E NO REF005 OUT ABCD 8000 XYZ5 XYZ55000
A Product F NO REF006 OUT ABCD 8000 XYZ5 XYZ55000
A Product G NO REF007 OUT ABCD 8000 XYZ5 XYZ55000
B Product H NO REF008 OUT ABCD 5000 XYZ1 XYZ11000

how to obtain this result in alteryx?

you can find the data in the Excel

Book1.xlsx

Ladarthure · Answer

Hi @aziz18,

this is not a union to my mind, it looks like a join, but without any specific rule on how to obtain the data I don't have a precise idea.

Could you explain how you add the 4 first lines from table A to each line on table B?