Good day,
I am new to Alteryx and am trying to determine the best way to compare and match two data sources. In the first, we have summary data for some of the entries from the second. Unfortuantely, I have no way to identify which items from the second add to the summary item in datasource 1. I am looking for a way of testing the combination of items from a specific date in source 2 to try to find the match for the summary line in 1. I have tried to include a sample scenario below. Lines 2 and 3 from source 2 would combine in this case, however we could have examples where there are more than two rows combining.
Line | Date | Location | Amount | Data source 1 |
1 | 2020-04-09 | 001 | 14135.20 | |
Line | Date | Source | Amount | Data Source 2 |
1 | 2020-04-09 | OP | 629.95 | |
2 | 2020-04-09 | OP | 5789.50 | |
3 | 2020-04-09 | OP | 8345.70 |
Am open to any recommendations on how I could try to develop this work flow
Hi @FredInniss
This is a bit of a hacky solution put together from the same logic that exists in other Community articles referencing 'permutations':
I'm sure there is probably a slicker way of doing this using iterative macros, but I've attached my solution which goes to 5x levels of combination depth (you can always add in more Append Fields tools as this is a pretty dynamic process otherwise.
As you can see below, dummy row 1 is the sum of match record 2+3, dummy row 2 is the sum of 1+4+5 in a matrix-style output (not sure what output format you were after):
I've attached the workflow, so take a look and let me know how it works for your challenge.
Thanks!
David Thorpe
Sales Engineering
I have a similar, but simpler issue. In my case the Data Source 2 all adds up to the total in Data Source 1, or at least it's supposed to. Is there a way to add a Test tool to validate that the value in Data Source 1 is equal to the sum of values in Data Source 2? I can't seem to find a way to configure the Test tool for this.