SUMing and Removing Similar but Different Data

[JacobGFMR]

Hello,

I am having trouble not including similar but different data from a data stream. 

I need to be able to keep all columns but I only need them when the SUM of all CASH is greater than 999,999.99 or less than -999,999.99. 

Occasionally, identical account numbers exists for different plans, and there is always new data multiple times per day. 

How can I SUM the total of cash per ACCT and PLAN while still keeping all columns and then removing all lines that don't fit the criteria (>999,999.99 or <-999,999,99)?

For this example, I need the last line as the CASH amount for all lines meets my criteria but I need to remove the other ACCT and PLAN and lines. 

ACCT	PLAN	F	S	TC	IC	CASH
999-99-9999F	74375	1234	1	436	10	1,939
999-99-9999F	74375	1234	9	436	10	75
999-99-9999F	74375	1234	9	436	10	-11
999-99-9999F	74375	1234	9	690	01	-181
999-99-9999F	74375	1234	9	436	10	-530
999-99-9999F	82831	AAAA	2	690	01	-1,622,858

[mceleavey]

Hi @JacobGFMR ,

for this to make sense can you mock up what your expected outcome is?

M.

[JacobGFMR]

@mceleavey ,

The expected outcome would only be:

999-99-9999F

82831

AAAA

2

690

01

-1,622,858

But there could be times when more than just these 2 PLAN numbers exist while other CASH amounts for all lines for a given ACCT and PLAN meet the criteria. 

Basically, I need to determine the SUM of CASH for ALL lines per PLAN and ACCT and then remove all lines where the SUM of CASH does not meet the criteria, while keeping all columns for the ones that do meet the criteria. 

[mceleavey]

@JacobGFMR ,

Why would you only have one line here?

Why wouldn't you have two lines as there are two combinations of ACCT and PLAN, both of which meet the criteria? Also, what you're saying does not match what you're showing, as you're showing the other columns, which means a different level of granularity to the data.

 Can you explain the logic as to why the other ACCT  and PLAN combination (999-99-9999F and 74375) would be removed, as the total cash at that level is 1292.

M.

[JacobGFMR]

@mceleavey ,

The only criteria I need is when the SUM of CASH for all lines per ACCT and PLAN is >999,999.99 or < -999,999.99. 

The total for ACCT 999-99-9999F, PLAN 74375 is 1292, which does not meet that criteria. 

So, I should only have 1 line for ACCT 999-99-9999F, PLAN 82831 as the SUM of CASH for all lines for that ACCT and PLAN meets the criteria. 

Maybe to simplify this, how can I add a column to the table that SUMs the CASH amounts for all lines per only ACCT and PLAN?

[mceleavey · Accepted solution]

@JacobGFMR ,

Gotcha.

Try the following:

This creates a sum at each ACCT/PLAN combination and joins it back after only taking the ones that fit the criteria.

Workflow attached.

Hope this helps,

M.