Hello
a little variation from Re: 4 way reconciliation - Alteryx Community....
I have 4 sources but they do not have the same set of columns. But I still need to compare and spit the differences for the similar columns.
Example:
Source1
| SEC ID | COUNTRY | CURRENCY | RATING | TYPE |
| sec0001 | US | USD | A | FI |
| sec0002 | AU | AUD | B | EQ |
| sec0003 | PH | PHP | C | EQ |
| sec0004 | MY | MYR | D | EQ |
| sec0005 | US | USD | E | EQ |
| sec0006 | HK | HKD | F | EQ |
| sec0007 | JP | JPY | G | EQ |
| sec0008 | GB | | H | FI |
| sec0009 | US | USD | I | EQ |
| sec0010 | US | USD | J | FI |
Source 2
| SEC ID | COUNTRY | CURRENCY | RATING |
| sec0001 | US | USD | A |
| sec0002 | AU | AUD | B |
| sec0003 | PH | PHP | C |
| sec0004 | MY | MYR | D |
| sec0005 | US | USD | E |
| sec0006 | HK | HKD | F |
| sec0007 | JP | JPY | G |
| sec0008 | GB | GBP | H |
| sec0009 | US | USD | I |
| sec0010 | US | USD | J |
Source 3
| SEC ID | COUNTRY | CURRENCY | TYPE | RATING | NAME |
| sec0001 | US | USD | EQ | A | SEC1 |
| sec0002 | AU | AUD | EQ | B | SEC2 |
| sec0003 | PH | PHP | EQ | C | SEC3 |
| sec0004 | MY | MYR | EQ | D | SEC4 |
| sec0005 | US | USD | EQ | E | SEC5 |
| sec0006 | CN | HKD | CSH | F | SEC6 |
| sec0007 | JP | JPY | EQ | G | SEC7 |
| sec0008 | GB | GBP | FI | H | SEC8 |
| sec0009 | US | USD | EQ | I | SEC9_TEST |
| sec0010 | US | USD | FI | K | SEC10 |
Source 4
| SEC ID | TYPE | EXCH | CURRENCY | NAME |
| sec0001 | EQ | XNYS | USD | SEC1 |
| sec0002 | EQ | XASX | AUD | SEC2 |
| sec0003 | EQ | XPHS | PHP | SEC3 |
| sec0004 | EQ | XKLS | MYR | SEC4 |
| sec0005 | EQ | XNAS | USD | SEC5 |
| sec0006 | EQ | XHKG | HKD | SEC6 |
| sec0007 | EQ | XTKS | JPY | SEC7 |
| sec0008 | FI | XLON | GBP | SEC8 |
| sec0009 | EQ | XNYS | USD | SEC9 |
| sec0010 | FI | XNAS | GBP | SEC10 |
So if I am going to compare my columns per sources, not all are common, some may be available to one source but not in the other:
| SOURCE1 | SOURCE2 | SOURCE3 | SOURCE4 |
| SEC ID | SEC ID | SEC ID | SEC ID |
| COUNTRY | COUNTRY | COUNTRY | |
| CURRENCY | CURRENCY | CURRENCY | CURRENCY |
| RATING | RATING | RATING | |
| TYPE | | TYPE | TYPE |
| | | | EXCH |
| | | NAME | NAME |
Here is the sample results I am expecting.
---It should only spit out the differences when it is comparable.
---If there is no data because it is completely not in that particular source, it should not break. (e.g. Country which is available in Source 1, 2, and 3 only, if the values all matched on these 3 sources, then it should not be in the results even though there is no data in Source 4. But if there's at least one discrepancy between Source 1,2 and 3, then it should appear as a break;;; OR another example is NAME where it is only available in Source 3 and 4, if both sources matched even there is no data in Source 1 and 2, they should not appear in the results, but if there is a break between Source 3 and 4 sources, then it should appear as a break...)
---If the data is null but the sources are comparable, it should appear in the results as a break (e.g. Currency is available in Source1,2,3,4, but the Currency is null in Source1, it should show in the differences results)
---if the data is only available in one Source (e.g. EXCH in Source4), ignore and should not appear in the differences results.
| SEC ID | FIELD | SOURCE1 | SOURCE2 | SOURCE 3 | SOURCE 4 |
| sec0001 | TYPE | FI | | EQ | EQ |
| sec0006 | COUNTRY | HK | HK | CN | |
| sec0008 | CURRENCY | | GBP | GBP | GBP |
| sec0009 | NAME | | | SEC9_TEST | SEC9 |
| sec0010 | CURRENCY | USD | USD | USD | GBP |
| sec0010 | RATING | J | J | K | |
Any help will be appreciated! Thank you!
