Keep conditional duplicate line

[Guitarmageddon]

Hello everyone,

I have a list of cash register transaction data. nothing too complicated, they have a unique key, some tender types, item counts, etc. the basic stuff. Here is a quick sampling of that data. However, Im running into a bit of a snag when it comes to duplicate lines. That first column is the unique ID of that transaction, just a concatenate of some of the data. There are some lines however, that are duplicated, which makes summarizing my counts to present some summary statistics very difficult.

For example, the first orange unique trans key is on there twice, because this was a debit transaction where cash was given back. How would I go about deleting dups EXCEPT the dup where TOTAL AMOUNT is positive? Basically, how to delete/keep conditional duplicates? Further down, you can see a "MST payment" aka a credit card payment done at a register terminal, but it lists two tenders. In those cases, I would keep whichever tender was positive. (the negative is whatever amount was paid down on the credit card, but the positive amount is whatever tender the customer used to make the payment)

I am thinking if I were to just select the "delete duplicates" function on the KEY column, it would just keep the first entry, so depending on the sort of the column, that could possibly work?

I tried to use the grouping function but again, It doesnt work very well with duplicates because I need to preserve at least one of those listed transaction types.

Hopefully what Im explaining is coming accross adequately, I would appreciate any tips here.

KEY	TYPE	STORE NUMBER	DATE	TRANSACTION_TIME	TERM	TRANSACTION	ITEMS	CASHIER	TENDER	CASH VS CHARGE	TOTAL AMOUNT
04100|2022-06-12|16:33:24|128|7684	SALE	04100	6/12/2022​	0.00:00:15​	128​	7684​	1	62266​	Visa	CREDIT	2​
04100|2022-06-12|16:34:22|128|7685	SALE	04100	6/12/2022​	0.00:00:23​	128​	7685​	1	62266​	American_Express	CREDIT	11​
04100|2022-06-12|16:36:03|128|7686	SALE	04100	6/12/2022​	0.00:00:25​	128​	7686​	2	62266​	Debit_Card	CREDIT	2​
04100|2022-06-12|09:59:00|131|6814	SALE	04100	6/12/2022​	0.00:13:50​	131​	6814​	7	113869​	Debit_Card	CREDIT	26​
04100|2022-06-12|09:59:00|131|6814	SALE	04100	6/12/2022​	0.00:13:50​	131​	6814​	7	113869​	Cash	CASH	-10​
04100|2022-06-12|10:12:58|131|6815	SALE	04100	6/12/2022​	0.00:04:33​	131​	6815​	3	113869​	Visa	CREDIT	17​
04100|2022-06-12|10:17:33|131|6816	SALE	04100	6/12/2022​	0.00:01:49​	131​	6816​	1	113869​	Cash	CASH	50​
04100|2022-06-12|10:19:39|131|6817	SALE	04100	6/12/2022​	0.00:03:26​	131​	6817​	1	113869​	Debit_Card	CREDIT	5​
04100|2022-06-12|10:23:07|131|6818	SALE	04100	6/12/2022​	0.00:01:16​	131​	6818​	10	113869​	American_Express	CREDIT	212​
04100|2022-06-12|10:24:29|131|6819	SALE	04100	6/12/2022​	0.00:05:34​	131​	6819​	1	113869​	Debit_Card	CREDIT	4​
04100|2022-06-12|12:14:05|131|6875	SALE	04100	6/12/2022​	0.00:01:07​	131​	6875​	4	113869​	MasterCard	CREDIT	41​
04100|2022-06-12|12:15:14|131|6876	SALE	04100	6/12/2022​	0.00:01:15​	131​	6876​	4	113869​	Debit_Card	CREDIT	17​
04100|2022-06-12|12:16:31|131|6877	SALE	04100	6/12/2022​	0.00:00:34​	131​	6877​	3	113869​	American_Express	CREDIT	21​
04100|2022-06-12|12:17:36|131|6879	MSC/THT_Payments	04100	6/12/2022​	0.00:00:15​	131​	6879​		113869​	Cash	CASH	100​
04100|2022-06-12|12:17:36|131|6879	MSC/THT_Payments	04100	6/12/2022​	0.00:00:15​	131​	6879​		113869​	Military_Star	CREDIT	-100​
04100|2022-06-12|12:17:58|131|6880	SALE	04100	6/12/2022​	0.00:01:23​	131​	6880​	4	113869​	Debit_Card	CREDIT	32​
04100|2022-06-12|13:14:22|131|6896	SALE	04100	6/12/2022​	0.00:02:46​	131​	6896​	3	113869​	Cash	CASH	20​
04100|2022-06-12|13:17:21|131|6897	SALE	04100	6/12/2022​	0.00:01:37​	131​	6897​	2	113869​	Visa	CREDIT	3​
04100|2022-06-12|13:19:00|131|6898	SALE	04100	6/12/2022​	0.00:00:27​	131​	6898​	2	113869​	Visa	CREDIT	3​
04100|2022-06-12|13:19:28|131|6899	SALE	04100	6/12/2022​	0.00:02:02​	131​	6899​	5	113869​	Debit_Card	CREDIT	102​
04100|2022-06-12|13:21:33|131|6900	SALE	04100	6/12/2022​	0.00:01:33​	131​	6900​	6	113869​	Debit_Card	CREDIT	57​
04100|2022-06-12|13:21:33|131|6900	SALE	04100	6/12/2022​	0.00:01:33​	131​	6900​	6	113869​	Cash	CASH	-40​
04100|2022-06-12|13:23:30|131|6901	MSC/THT_Payments	04100	6/12/2022​	0.00:00:33​	131​	6901​		113869​	Cash	CASH	100​
04100|2022-06-12|13:23:30|131|6901	MSC/THT_Payments	04100	6/12/2022​	0.00:00:33​	131​	6901​		113869​	Military_Star	CREDIT	-100​

 

[johnny51981]

Actually...let's try the Group By one more time.

In this Group By, select Advanced. In the first Drop Down on the top, select your Unique ID. On the Bottom, add in "Grand Total" as New Column Name set it to Max Operation, and then select the Grand Total from that drop down.
Just below that, add in "All Data" as a second New Column Name, set it to All Rows Operation.

Click OK.

This should result in 3 columns: Unique ID, Grand Total, and All Data. Each Unique ID will be listed only once, the Grand Total will be the highest value for that Unique ID (if there is more than one entry). That takes care of the dupicates.

Now, expand the All Data column, similar to how your expand a Merged Query, select the columns you want to return. Et voila.