Using CONTAINS function while joining

[ssubramanian]

Hi,

I have a requirement where -> Table A has Column = A_ID and Dept  ID and;  Table B has column B_ID and Dept_ID as below

Table A
ID	Dept
1	A
1	B
1	C
2	A
3	C
3	D
3	E

Table B
ID	Dept
1	A,B
2	A
3	C,D

I need to join table A to B on ID and select the Depts in Table A that is present in Table B for the respective ID. For eg, ID = 1 Dept = A, ID = 1 and Dept = B is selected from Table A for ID = 1 as Depts - A and B are the only ID's listed in Table B

So I need to recreate this query in Alteryx:

" Table A INNER JOIN Table B ON A.ID = B.ID and Contains(B.Dept, A.Dept)"

Please suggest  a solution.

[DavidP]

How about this: Join on ID, then use a filter with the statement for B_Dept  Contains([B_Dept],[A_Dept])

[ponraj]

Here is the sample workflow for your case. Hope this is helpful. 

WorkflowResults

[ssubramanian]

Thank you David. It worked!

[ssubramanian]

Thank you Ponraj for attaching the workflow. Thank you for showing me another method of achieving it.

[ponraj]

You can mark the workflow which really helped you to achieve the result as solution. 

[Traci_Scherer]

Came across your solution while searching how to use Advanced Join with CONTAINS. Yours works perfectly and is much more straightforward. Your gift of knowledge is still giving!