Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Compare two large excel worksheets with detail

JTCairns
8 - Asteroid

Hi All,

I am currently working on re-doing a very manual excel process with Alteryx. I have been struggling to find an effective way of checking my results.

 

The end data set comes from combining several input data sets and ends up being ~80,000 records with 100+ columns, I need to be able to check my results (using old data) with a version that is correct from the current process. However I need to check each record in each column and see clearly where things have went wrong.

 

I have tried joins on joins etc but wondered if anyone had any innovative ideas? I also need to check EVERY record so sampling doesn't work.

 

Any ideas greatly appreciated

 

Many Thanks

6 REPLIES 6
BenMoss
ACE Emeritus
ACE Emeritus

I would tranpose each data stream then you only have to do a single join on the column name and 'recordID' before doing a formula to identify the difference.

 

This is the exact thing I am doing now.

This will give you an 8million row table but that should be fine for alteryx to handle.

JTCairns
8 - Asteroid

Hi @BenMoss,

 

Thanks for the speedy reply, could you post an example please im not sure how this would work (transpose is not on e of my more used tools) 

 

Many Thanks,

BenMoss
ACE Emeritus
ACE Emeritus

No problem, see attached.

 

 

JTCairns
8 - Asteroid

Thanks a lot @BenMoss that has worked great, just one last thing how do i revert the transpose, im guessing cross tab but what fields etc.

 

You're my new best friend

 

 

BenMoss
ACE Emeritus
ACE Emeritus

To revert you should use the cross-tab as you mention.

 

Your key field would be your ID.

 

The Header field would be your 'name' column.

 

And the value field would be either of the value fields, depending on which you want to take through.

Ben

ShantanuDagar
8 - Asteroid

Hey bro.

 

Does the formula in the filter tool you have added work on string matches too?

 

Like changes in the address field or name field?

Labels