Hi everyone,
Below I'm pasting initial Data and the desired result. The logic to apply is If the Employee Name subsequent rows are equal (Ex: APPLEBEE, CAROL=APPLEBEE, CAROL), and REPORT column subsequent rows are FM08-TSTL410A, and NEW Number subsequent rows are equal (Ex:0001=0001), and then the Difference Variance column contains a zero and subsequent row number is different to zero.
I really appreciate the help you can provide.
Employee Name | REPORT | DATE | NEW Number | DEBITS | CREDITS | Difference Variance |
APPLEBEE, CAROL | FM08 | 12/15/2023 | 0001 | 492.96 | 589.21 | 0 |
APPLEBEE, CAROL | TSTL410A | 12/15/2023 | 0001 | 589.21 | 674.51 | 96.25 |
BOYLE, SANDRA | FM08 | 12/15/2023 | 0004 | 0 | 5,000.00 | 0 |
BUREAU, DIANE | FM08 | 12/15/2023 | 0004 | 665.72 | 662.07 | 0 |
CALHOUN, JOHN | TSTL410A | 12/15/2023 | 771.01 | 20254 | 0 | |
CALHOUN, JOHN | FM08 | 12/15/2023 | 0001 | 6,425.84 | 0 | 0 |
CALHOUN, JOHN | FM08 | 12/15/2023 | 0017 | 8,450,006.77 | 8,430,523.78 | 0 |
COUTURIER, CRISTAL | FM08 | 12/15/2023 | 0004 | 757,016.22 | 21,777.12 | 0 |
CRANSTON, JOSHUA | FM08 | 12/15/2023 | 0001 | 250 | 0 | 0 |
CRANSTON, JOSHUA | FM08 | 12/15/2023 | 0003 | 0 | 5,599.03 | 0 |
CRANSTON, JOSHUA | FM08 | 12/15/2023 | 0004 | 5,349.03 | 0 | 0 |
DAY, PEG | FM08 | 12/15/2023 | 0001 | 0 | 71,039.62 | 0 |
DAY, PEG | FM08 | 12/15/2023 | 0002 | 0 | 30,000.00 | 0 |
DAY, PEG | FM08 | 12/15/2023 | 0003 | 0 | 523,300.03 | 0 |
DAY, PEG | FM08 | 12/15/2023 | 0004 | 0 | 718,520.88 | 0 |
DAY, PEG | FM08 | 12/15/2023 | 0011 | 5,000.00 | 12,000.00 | 0 |
FANELLI, FELICITA | TSTL410A | 12/15/2023 | 247597.95 | 495422.76 | 0 | |
GARRISON, LASHUNDA | FM08 | 12/15/2023 | 0004 | 3,983.26 | 0 | 0 |
GRONDIN, DOLORES | FM08 | 12/15/2023 | 0001 | 0 | 298,284.69 | 0 |
GRONDIN, DOLORES | FM08 | 12/15/2023 | 0003 | 500 | 12,755.89 | 0 |
GRONDIN, DOLORES | FM08 | 12/15/2023 | 0004 | 4,953,388.56 | 64,186,063.83 | 0 |
GRONDIN, DOLORES | TSTL410A | 12/15/2023 | 0004 | 73599423.32 | 14366248.05 | 59232675.27 |
GRONDIN, DOLORES | FM08 | 12/15/2023 | 0011 | 0 | 35,385.36 | 0 |
GRONDIN, DOLORES | FM08 | 12/15/2023 | 0018 | 0.85 | 0 | 0 |
HARRINGTON, ANDREA | FM08 | 12/15/2023 | 0001 | 6,363.83 | 5,163.83 | 0 |
HARRINGTON, ANDREA | FM08 | 12/15/2023 | 0003 | 14,358.84 | 7,086.84 | 0 |
HARRINGTON, ANDREA | FM08 | 12/15/2023 | 0004 | 121,822.99 | 12,687.25 | 0 |
HICKLING, ELAINE | FM08 | 12/15/2023 | 0004 | 7,831,128.37 | 6,127,867.41 | 0 |
MAJOR, STACY | TSTL410A | 12/15/2023 | 0 | 1154935000 | 0 | |
MARINGASPAR, JENNIFER | FM08 | 12/15/2023 | 0001 | 100 | 0 | 0 |
MARINGASPAR, JENNIFER | FM08 | 12/15/2023 | 0003 | 247,448.93 | 0 | 0 |
MARINGASPAR, JENNIFER | FM08 | 12/15/2023 | 0004 | 37,319.98 | 22,693.45 | 0 |
MARINGASPAR, JENNIFER | FM08 | 12/15/2023 | 0011 | 8,000.00 | 0 | 0 |
MARUVADA, VINDHYA | TSTL410A | 12/15/2023 | 12964.51 | 25929.02 | 0 | |
MCRAE, SHAQUITA | FM08 | 12/15/2023 | 0003 | 56,451.53 | 11,212.65 | 0 |
MEISNER, AMANDA | FM08 | 12/15/2023 | 0001 | 9,038.54 | 0 | 0 |
MEISNER, AMANDA | FM08 | 12/15/2023 | 0002 | 456,187.75 | 455,884.04 | 0 |
MEISNER, AMANDA | FM08 | 12/15/2023 | 0003 | 0 | 100,000.00 | 0 |
MEISNER, AMANDA | FM08 | 12/15/2023 | 0004 | 55.46 | 186,953.42 | 0 |
MIVILLE, RACHELLE | FM08 | 12/15/2023 | 0001 | 0 | 13,660.50 | 0 |
MIVILLE, RACHELLE | FM08 | 12/15/2023 | 0002 | 2.67 | 0 | 0 |
MIVILLE, RACHELLE | FM08 | 12/15/2023 | 0003 | 2,444.63 | 0 | 0 |
MIVILLE, RACHELLE | FM08 | 12/15/2023 | 0004 | 8,771,258.55 | 3,864,305.42 | 0 |
MOODY, RIANA | TSTL410A | 12/15/2023 | 0004 | 0 | 2330 | 0 |
MURPHY, HAYLEY | FM08 | 12/15/2023 | 0001 | 32,587.33 | 1,099.07 | 0 |
MURPHY, HAYLEY | FM08 | 12/15/2023 | 0002 | 198,121.72 | 164.43 | 0 |
MURPHY, HAYLEY | FM08 | 12/15/2023 | 0003 | 2,951.64 | 2,936.15 | 0 |
MURPHY, HAYLEY | FM08 | 12/15/2023 | 0004 | 1,015,711.78 | 3,418.94 | 0 |
MURPHY, HAYLEY | FM08 | 12/15/2023 | 0018 | 95,363.95 | 164.15 | 0 |
PASTRANA, THERESA | FM08 | 12/15/2023 | 0003 | 5,021,437.47 | 2,413,367.72 | 0 |
PASTRANA, THERESA | FM08 | 12/15/2023 | 0004 | 1,771,673.84 | 1,430,430.85 | 0 |
PHILSON, KAYLA | FM08 | 12/15/2023 | 0002 | 25,000.00 | 0 | 0 |
PHILSON, KAYLA | FM08 | 12/15/2023 | 0003 | 1,000.00 | 13,320.00 | 0 |
PHILSON, KAYLA | FM08 | 12/15/2023 | 0004 | 9,719,234.93 | 5,014,729.43 | 0 |
PYLES, QUASTACIOUS | FM08 | 12/15/2023 | 0001 | 260.8 | 0 | 0 |
QUIRRION, DENISE | FM08 | 12/15/2023 | 0001 | 605 | 0 | 0 |
QUIRRION, DENISE | FM08 | 12/15/2023 | 0003 | 4,423.95 | 0 | 0 |
QUIRRION, DENISE | FM08 | 12/15/2023 | 0004 | 94,815.79 | 95.06 | 0 |
QUIRRION, DENISE | FM08 | 12/15/2023 | 0011 | 5,311.50 | 0 | 0 |
SAINT-PAUL, BAPTISTE | FM08 | 12/15/2023 | 0002 | 0 | 207.03 | 0 |
SAINT-PAUL, BAPTISTE | FM08 | 12/15/2023 | 0003 | 7,887.86 | 6,687.86 | 0 |
SAINT-PAUL, BAPTISTE | FM08 | 12/15/2023 | 0004 | 15,473.99 | 25,030.43 | 0 |
SANDERS, DANYALE | FM08 | 12/15/2023 | 0001 | 10,000.00 | 0 | 0 |
SANDERS, DANYALE | FM08 | 12/15/2023 | 0003 | 191,430.39 | 11,895.74 | 0 |
SANDERS, DANYALE | FM08 | 12/15/2023 | 0004 | 315,036.84 | 1,359.02 | 0 |
SHELL, QUADREE | FM08 | 12/15/2023 | 0003 | 41,529.16 | 0 | 0 |
SHELL, QUADREE | FM08 | 12/15/2023 | 0004 | 4,192.40 | 10,116.28 | 0 |
SHELL, QUADREE | TSTL410A | 12/15/2023 | 0004 | 100315.65 | 95002.86 | 5923.88 |
SHELL, QUADREE | FM08 | 12/15/2023 | 0018 | 0 | 1,777.24 | 0 |
SHORT, JOHN | FM08 | 12/15/2023 | 0003 | 33,257.95 | 8,092.34 | 0 |
SHORT, JOHN | FM08 | 12/15/2023 | 0004 | 11,720.61 | 1,916.40 | 0 |
SHORT, JOHN | FM08 | 12/15/2023 | 0011 | 600 | 0 | 0 |
SULLIVAN, BRITTANY | FM08 | 12/15/2023 | 0003 | 0 | 59,680.67 | 0 |
TAMBUSSI, ROSEMARY | FM08 | 12/15/2023 | 0002 | 0 | 5,592.07 | 0 |
TAMBUSSI, ROSEMARY | FM08 | 12/15/2023 | 0004 | 4,446,124.84 | 4,062,475.30 | 0 |
THIBAULR, FAYE | FM08 | 12/15/2023 | 0017 | 62,944,310.28 | 0 | 0 |
WELENC, DIANE | TSTL410A | 12/15/2023 | 103326.58 | 225 | 0 | |
WRIGHT, MELISSA | FM08 | 12/15/2023 | 0003 | 773,198.05 | 0 | 0 |
YELKONDAWAR, GIRISH | FM08 | 12/15/2023 | 0001 | 2,792.89 | 0 | 0 |
YELKONDAWAR, GIRISH | FM08 | 12/15/2023 | 0002 | 72,381.37 | 165.26 | 0 |
YELKONDAWAR, GIRISH | FM08 | 12/15/2023 | 0003 | 1,166.57 | 0 | 0 |
YELKONDAWAR, GIRISH | FM08 | 12/15/2023 | 0004 | 770,805.20 | 613.23 | 0 |
And I would like Alteryx to obtain the following result:
Employee Name | REPORT | DATE | NEW Number | DEBITS | CREDITS | Difference Variance |
APPLEBEE, CAROL | FM08 | 12/15/2023 | 0001 | 492.96 | 589.21 | 0 |
APPLEBEE, CAROL | TSTL410A | 12/15/2023 | 0001 | 589.21 | 674.51 | 96.25 |
GRONDIN, DOLORES | FM08 | 12/15/2023 | 0004 | 4,953,388.56 | 64,186,063.83 | 0 |
GRONDIN, DOLORES | TSTL410A | 12/15/2023 | 0004 | 73599423.32 | 14366248.05 | 59232675.27 |
SHELL, QUADREE | FM08 | 12/15/2023 | 0004 | 4,192.40 | 10,116.28 | 0 |
SHELL, QUADREE | TSTL410A | 12/15/2023 | 0004 | 100315.65 | 95002.86 | 5923.88 |
@araquecastro try using the unique tool and selecting Employee name. Then using the duplicate output join that with the starting data set joining on the employee name and deselecting the fields from the duplicate output.
Let me know if this helps