I have 2 data files: DataA and DataB
DataA is a huge data file consisting of many columns and rows. Here's an example of DataA with only the relevant columns:
| Acc_num | DK | DK_Acc_A |
| 1001.1 | D | D_1001.1 |
| 1004.1 | D | D_1004.1 |
| 1100.1 | K | K_1100.1 |
| 1002.1 | K | K_1002.1 |
DataB only have a few columns and rows. Example of DataB:
| Book_num | Acc_num | DK | DK_Acc_B |
| 1 | 1001.1 | D | D_1001.1 |
| 1 | 1001.2 | D | D_1001.2 |
| 1 | 1002.1 | K | K_1002.1 |
| 1 | 1002.3 | K | K_1002.3 |
| 2 | 1004.1 | D | D_1004.1 |
| 2 | 1100.1 | K | K_1100.1 |
I want to get the value from column Book_num in DataB for rows in DataA whose DK_Acc value matches in both datasets. Basically what I want to do is this:
For (number of rows in DataA):
For (number of rows in DataB):
if (DK_Acc_A = DK_Acc_B) then
# Make new column in DataA called Match, and put the value of column Book_num from DataB
DataA.Match = DataB.Book_num
endif
next
next
Expected DataA output:
| Acc num | DK | DK_Acc_A | Match |
| 1001.1 | D | D_1001.1 | 1 |
| 1004.1 | D | D_1004.1 | 2 |
| 1100.1 | K | K_1100.1 | 2 |
| 1002.1 | K | K_1002.1 | 1 |
Idk how to do this in Alteryx at all. I watched videos and found alteryx questions about using the Multirow formula tool, but it only gets like 2 or 3 rows before and after the active row. But what I want to do is to iterate through all rows to find the match.
I want to avoid using the python tool because this workflow will be passed on to someone else who doesn't programming at all, so I want to keep it simple using only alteryx tools.
