Compare two files with same columns
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I've two inputs (A and B) and would like to compare and update data having A as primary. For example:
Input A
Input B
Output
- ID and Year are the key fields
- I'd like to update Address and Contact. If the field is null on B, keep A.
Real tables actually have 75 fields. Is there a way to create a single template formula for all 75 fields like IIF(Isnull([Field_B]), [Field_A], [Field_B]) instead of creating a formula for each field (one giant formula tool with 75 formulas) - nor using batch macros.
Solved! Go to Solution.
- Labels:
- Datasets
- Dynamic Processing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Would you like to try my CReW Delta macro to find the changes?
https://gallery.alteryx.com/#!app/CReW-Delta--Alpha-/5b41141b826fd3116445fee8
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The easiest way to do this is to transpose both datasets so your numerous fields are all stacked on top of each other. You can then filter for where you have nulls in A and then join them with B to replace them. All that is needed is to union them back together and then cross tab to get your full data structure back.
Hope this helps. Let me know how you get on.
Luke
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@LukeM this works!
1. Transpose using ID, Year as Key field and the rest of the fields as Data
2. Filtered out empty data (Value)
3. Cross Tab using ID, Year as Group Data fields
4. Join: on J output I deselect L fields and keep/rename R fields removing the "Right_" prefix.
5. Union.
@MarqueeCrew also thanks for the macro. I see this isn't in the CreW Macro (2016) but I imported already. I'll surely use it in the near future.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Awesome, great news!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This flow sounds like exactly what I am looking for, but I don't have the latest version of Alteryx. What version are you using? Is there a way to save this in a lower version or have created in the lower version to be able to use?
Thank you.
Jackie
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I made it in 2018.4.
You can always downgrade the version of the file manually by editing the file - change the version number in the XML code.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you, I will try that.
Have a great day.
