Comparing 2 Periods of Data and Seeing Difference between both files
- 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
Hi All,
I've been trying to look through other threads like this, but I'm not sure if what I'm looking for is a direct comparison to them.
I have 2 periods of Data from Oct and Nov. I would like to compare each customer data record to see what changes I have in my forecasted customer prices in both files. I guess a unique record for each data point would be a concat of the Bill to ID, Bill to ID Name, Rollup Level Code, Desc, Prod Level 1 Code, Desc, Prod Level 2 Code, Desc, Cat #, and Cat Desc.
I want to ideally see which records stayed exactly the same between both files (in terms of the New Price charged to them) with a column showing a flag stating 'Same' or a Difference column showing a '$0' difference. Any records where there is a difference, I would ideally like to have that Difference column showing the $ difference between both files. There could be records that exist in only Oct or only Nov, so I would like a flag to show that as well. I tried using Joins and joined the left and right sides, but honestly not sure if I'm doing it correctly. I'm not sure how to get the layout I'm looking for.
Customer Data columns....... Old Price New Price Incr $ Difference ($) Exist in which file (Both, Oct only, Nov only)
I feel like this should be a relatively easy thing to do, but I'm running into a lot of issues. Any help would be greatly appreciated.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I usually do something like this ....
Join on ALL the fields that make sense (you've stated 10 but you might not need the desc fields).
If Oct is the Left side of the join and Nov is the Right side of the join then ...
Using a join tool the L output is "Oct only", the R output is "Nov only" and the J output you can then compare the values and see if there's a change between the months.
After all of this, union the results together and you have the result you're after.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @cmcclellan,
I think I attempted doing what you're saying. However, I'm not sure if this is an error in my own data or my concat I am basing this off of, but when using the Summarize tools for my Left and Right outputs, I wouldn't expect to see any records with a PI Date of July, Aug, or September in either of the outputs that didn't match. And in the Right output where my November file is I wouldn't expect to see any October records that didn't match since those are my Actual numbers that should not have changed in either of the files. Not quite sure what i'm doing wrong with this =/ . I've attached the workflow I have at the moment to this message.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
https://gallery.alteryx.com/#!app/CReW-Delta--Alpha-/5b41141b826fd3116445fee8
You can read about it here:
http://www.chaosreignswithin.com/2018/07/crew-needs-your-opinion.html?m=1
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
