Find and replace: append unknown or dynamic fields

[taniajurik]

Hi,

I am new to Alteryx and would need your help please. 

I have to tables that I would like to join. I need to get monthly data from the second table to the first one

Key 1	# Invoices
Austria123	4
Switzerland234	12
Austria873	32
Belgium890	1
USA901	84
Germany281	17
Japan814	9

Key 1	# Invoices  APR 2024	# Invoices  MAY 2024	# Invoices  JUN 2024	# Invoices  JUL 2024
Austria123	9	10	4	7
Austria873	9	6	10	2
Belgium890	2	9	10	3
Switzerland234	0	8	5	6
USA901	8	9	6	4

Simply doing it with Find and Replace: append fields to record. 

But next month layout of table 2 will change and will have one more column: AUG 2024.

I would like to avoid selecting new field manually. With the Select tool I choose Dynamic or Unknown field option, those fields are passed then to the Append fields list in Find and Replace tool, but checkbox still needs to be manually ticked. 

 

 

 

Please advise how to solve this.

Thank you!

[Ankur_BDO · Accepted solution]

Thanks for sharing your solution. For combining the column from two tables horizontally, you should use the Join Tool, and apply join on "Key 1 " field. I have added a Union tool to show all item even if they are not present in the monthly invoices. Sort tool takes care of the List order. With this flow you can change the input file and the new month's data will come automatically. Replace the Text Input tools with your Input tools. See the sample workflow in the attachment.

If you got your answer, mark the response as Solution.

[OTrieger]

Hi @taniajurik 

You are on the correct path almost there. If you will input the file with the months each time select the most up to date file and then append the new month to that file then you will not have an issue as long as that new month have the same name each month and then after appending it you could rename it with the new month.

[TUSHAR050392 · Accepted solution]

Hi, In your case, I will use the join tool first table will be joined to left side and second to right on Key1. After this, use the union tool and input J & L stream from the join tool and that way you won't miss any values from the first table if they have any match in the second table. This will also ensure that any new column that you add to the second table automatically flows without the need to manually select. Hope this helps and explains the reasoning.

[taniajurik]

Thank you, it worked!