Comparing 2 Periods of Data and Seeing Difference between both files

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.

Sample Data 2.xlsx

Datasets

Input

Output

Parse

Macros

Help

Dynamic Processing

Expression

Data Investigation

Iterative Macro

Accepted answers

cmcclellan

I would do something like this (attached)

Sample Workflow 1.yxmd

All comments

cmcclellan

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.

hydrogurl01

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.

Sample Workflow 1.yxzp

cmcclellan

I would do something like this (attached)

Sample Workflow 1.yxmd

MarqueeCrew

You might want to look at the CReW Delta macro.

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280