Help - Find replace or join or something else?

[jcsoh]

Hi all,

If I want to match the data from "Total" column in "Table A" to the Company Name and have the data populate an empty column in "Final total" under "Table B", how should I do it? Table A and Table B are two separate input datas.

I would like the final output to be like Table C.

Table A

Company Name	Total
Company A	1111111
Company B	222222
Company C	333333
Company D	444444

Table B

Company Name	Final total
Company A
Company B
Company C
Company D

Table C

Company Name	Final total
Company A	1111111
Company B	222222
Company C	333333
Company D	444444

Cheers,

J

[JosephSerpis · Accepted solution]

Hi @jcsoh I mocked up a few examples on how you can do this. Let me know what you think?

[jcsoh]

Thanks @JosephSerpis for the prompt solution! That works. I have another question that is linked to this, I'll post it in a separate discussion post.

[jcsoh]

Hi @JosephSerpis / all, I have question. If the order of the company name for two input data is different from Table A and Table B, how should I match the data according to the company name? Final output is Table C.

Table A

Company Name	Final total
Company A
Company B
Company C
Company D

Table B

Company Name	Total
Company D	444444
Company B	222222
Company C	333333
Company A	1111111

Table C

Company Name	Final total
Company A	1111111
Company B	222222
Company C	333333
Company D	444444

Cheers,

J

[grazitti_sapna]

Hi @jcsoh,

In case you have small data set for your table B i suggest you use Find and Replace with table B in your find port that would be efficient, but if your data set is quite large i suggest you use the join tool to join tables a and B based on the company Name and then if you want order to be maintained as table A you can use Sort tool and sort your data on company Name.

I hope this helps with your query.

[JosephSerpis · Accepted solution]

Hi @jcsoh the Join approach that I demonstrated will sort your data into alphabetic or numerical order depending on the data fields/columns you are joining. In your example using a Join tool no matter which way you input your data the output should look like Table C. The Find and Replace approach will respect the order of the data put into the F (Find) Anchor. Therefore if you connected Table B to the F anchor and Table A to R anchor of the Find and Replace tool it will stay in the order of Table B. You can use a Sort tool though after Find and Replace tool to match Table C. 

I will add one caveat if you are using Alteryx Designer 2020.2 with the AMP engine turned on then the Join tool approach will not be outputted in alphabetic order and you would need to use a Sort tool afterwards.