SUMing and Removing Similar but Different Data

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

Help

Accepted answers

mceleavey

@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,

grouping sum_mceleavey.yxmd

All comments

mceleavey

Hi @JacobGFMR ,

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

JacobGFMR

@mceleavey ,

The expected outcome would only be:

999-99-9999F

82831

AAAA

690

-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.

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

@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,

grouping sum_mceleavey.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703