Subtracting two files with large datasets

vizwiz1

Hello Community,

I am fairly new to Alteryx and am having a hard time figuring out a problem.

I have 2 files (csv):

Total File sample (File A)- This file has all the items in a system (a superset if you will) (100M rows)

Failed items (File - This file has all the failed items which did not enter the system (300,000 rows)

Now, I want to get the passed items, which are the ones which are not in File B, but in File A (essentially A-B).

Also, I want to add a column which says if the item Passed/Failed to ultimately calculate the pass %.

How would I get one file with all the items and a column which mentions if an item passed/failed on a particular date?

Any help is much appreciated.

I have attached the 2 sample files

Failed items.xlsx

Total File sample.xlsx

Join

Database Connection

Accepted answers

MarqueeCrew

@vizwiz1,

Here's a starter workflow to help you.

Cheers,

Mark

subtracting.yxmd

LordNeilLord

Hey @vizwiz1

A join tool will do this perfectly....anything from the left will be a "pass" (they only appear in the Total file)...Anything from the join will be fails (they appear in both Total and Fail)...here's an example..

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

PassFail.yxzp

All comments

MarqueeCrew

@vizwiz1,

Here's a starter workflow to help you.

Cheers,

Mark

subtracting.yxmd

LordNeilLord

Hey @vizwiz1

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

PassFail.yxzp

vizwiz1

Thanks LordNeilLord !

As i mentioned I am joining csv files with a large number of rows (about 100M).

I followed the process mentioned, but I have some questions:

1. Should item number be a string or an int32 (it is a 6 digit item number)? I ask this as i tried doing both and int32 takes significantly longer than string to run and the file size blows up as well.

Should insert date be a date field or it can be a string as well?

2. I am using 3 join conditions (item number, date and source). Does this create a Cartesian join?

3. In my output, the countd(pass+fail) does not match the total countd of item numbers. I suspect this maybe because of some noise in the data. What data cleaning would you recommend? I am currently only clearing whitespaces.

Thanks a lot for your help!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335