Hi, Below is my data Clr doc_#Clearing_DateInvoice_Amount8521288920-Oct-14-205.348521288920-Oct-14-205.344820329295-Nov-14319.814820329295-Nov-14-319.818351811143-Nov-14331.6525749518-Dec-14-10475.7025749518-Dec-1410475.7025749518-Dec-1410475.7034389809819-Dec-142000.0034389809819-Dec-14-1000.0034389809819-Dec-14-1000.0034389809819-Dec-14500.0023709-Dec-1411132.0023709-Dec-1420.0033088-Oct-142000.0033088-Oct-14-1000.0033088-Oct-14-1000.0012348-Oct-14-2000.0012348-Oct-141000.0012348-Oct-141000.00 Expected Output: Clr doc_#Clearing_DateInvoice_AmountManual flag8521288920-Oct-14-205.34I8521288920-Oct-14-205.34I8351811143-Nov-14331.65I25749518-Dec-1410475.70I34389809819-Dec-14500.00I23709-Dec-1411132.00I23709-Dec-1420.00I Logic is groupby doc_num and date: eliminate those records where sum of amount of n rows for each doc# is zero for Example: for doc_num 257495: we have 3 records but if we sum up invoice amount (-10475.70)+(10475.70)+(10475.70) = 10475.70, so instead of 3 records, I should get only one record that has data like below and create a column as Manual flag that has I=Include, E= Exclude 257495 18-Dec-14 10475.70 I

Eliminate records based on condition

Hi, Below is my data

Clr doc_#	Clearing_Date	Invoice_Amount
85212889	20-Oct-14	-205.34
85212889	20-Oct-14	-205.34
482032929	5-Nov-14	319.81
482032929	5-Nov-14	-319.81
835181114	3-Nov-14	331.65
257495	18-Dec-14	-10475.70
257495	18-Dec-14	10475.70
257495	18-Dec-14	10475.70
343898098	19-Dec-14	2000.00
343898098	19-Dec-14	-1000.00
343898098	19-Dec-14	-1000.00
343898098	19-Dec-14	500.00
2370	9-Dec-14	11132.00
2370	9-Dec-14	20.00
3308	8-Oct-14	2000.00
3308	8-Oct-14	-1000.00
3308	8-Oct-14	-1000.00
1234	8-Oct-14	-2000.00
1234	8-Oct-14	1000.00
1234	8-Oct-14	1000.00

Expected Output:

Clr doc_#	Clearing_Date	Invoice_Amount	Manual flag
85212889	20-Oct-14	-205.34	I
85212889	20-Oct-14	-205.34	I
835181114	3-Nov-14	331.65	I
257495	18-Dec-14	10475.70	I
343898098	19-Dec-14	500.00	I
2370	9-Dec-14	11132.00	I
2370	9-Dec-14	20.00	I

Logic is groupby doc_num and date: eliminate those records where sum of amount of n rows for each doc# is zero

for Example: for doc_num 257495: we have 3 records but if we sum up invoice amount (-10475.70)+(10475.70)+(10475.70) = 10475.70, so instead of 3 records, I should get only one record that has data like below and create a column as Manual flag that has I=Include, E= Exclude

257495 18-Dec-14 10475.70 I

Common Use Cases

Accepted answers

mceleavey

Ok, so I looked through it and determined that what you are trying to do is nett off invoices to credits, and so only nett off those where positive and negative values exist.

If there are only negative values, or only positive values, then show all rows.

I've attached the workflow which achieves this.

Let me know if this is correct or if I've misunderstood the logic.

For Shaaz.yxmd

All comments

Shaaz

Attached is the workflow that I tried but for a couple of line items I'm not getting expected output

Test.yxmd

mceleavey

Could you just clear why you want some to show as a single line and others to show as two lines?

For example, document 85212889 should show a single line of -410.68 but you want to show two lines of -205.34? What is the logic that differentiates this particular line?

Shaaz

Yes, I would like have two lines for doc num 85212889 because if we see the amount for that particular doc num both has -205.34, so if we add both the amounts sum is not zero.

I can give a scenario:

Bank pays to customer $100 by mistake with doc num A101 and on some date (Lets take 10/Aug/2017). Then Customer realises its by mistake, so Customer pays back to the bank $100 with same doc num A101 and on same date. So it is zero, neither a loss to bank nor to customer. So, I would like to eliminate these two records.

There could be chance where only bank pays to customer twice $100 but customer wont payback. In this case, I would like to have both the records in my dataset.

mceleavey

Ok, so I looked through it and determined that what you are trying to do is nett off invoices to credits, and so only nett off those where positive and negative values exist.

If there are only negative values, or only positive values, then show all rows.

I've attached the workflow which achieves this.

Let me know if this is correct or if I've misunderstood the logic.

For Shaaz.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280