Difficult group pairs problem

[mrmookster]

I have a report which shows transactions. Some of these transaction have been paired off by a corresponding -ve amount. I want to only show unpaired rows.

ie
19.00
19.00
19.00
-19.00
-19.00

would show 1 row 19.00

500
-500

would show no rows

500
-500
32.00

would show one row 32.00

can i do this through grouping / formulas

 

[lbass]

Using your examples, you could insert a sum on the values at the group level and suppress the details, and you would get the correct display. If the issue is more complex, you should provide more sample data and explain a little more about what this all means.

-LB

 

[synapsevampire]

Group by the field and use 3 formulas.

In the group header use:
whileprintingrecords;
numbervar OddVal:= {table.field};
numbervar counter:=0;

In the Details use:
whileprintingrecords;
numbervar counter;
Counter:=Counter+1;

In the group footer use:
whileprintingrecords;
numbervar OddVal

Suppress the group header and the details.

Right click the group footer and select format section and in the X2 next to suppress place:

whileprintingrecords
numbervar counter;
remainder(counter,2) = 0

-k

 

[synapsevampire]

I think in LBs example she meant that you might group by the field, suppress the details and group footer, place the field in the group header and then in the x2 next to suppress in the group header place:

reaminder(count({table.value},{table.value}),2) = 0

-k

 

[lbass]

No, I didn't mean that. The examples above seemed to be grouped by some unseen field. If a sum was inserted on the shown values at the group level and the details suppressed, the correct value would be shown in the group footer--again, assuming that the actual case is not more complex than this.

-LB

 

[mrmookster]

Sorry it was a bad example. What I was after was more complicated than a sum. But thanks synapsevampire you guessed what i was after and actually the second option seems to work fine.

so

aaa bbb ccc 19.00
aaa bbb ccc 19.00
aaa bbb ccc -19.00

would show the one row
aaa bbb ccc 19.00

 

[lbass]

Sorry, still don't see why you couldn't drag the fields "aaa bbb ccc" into the group footer and insert a sum on the value, but maybe there's more to it that isn't shown.

-LB

 

[synapsevampire]

LB: They didn't want a sum, they only want to show any odd values.

So you need to check for the occurrence of an odd number of values, if it exists, show the value of the field, otherwise suppress.

-k

 

[lbass]

The sum would have shown that in the above displayed instances--but I yield.

-LB

 

[mrmookster]

Sorry guys this still isn't quite working as I'd like. Apologies for the poor data example previously.

The code abve (either versions) returns odd rows where there is a match

ie
19
-19
19
20
-20

would return one row 19

but if i have a number of unmatched rows
20
20
20
20

i need to return all 4 rows (synapsevampire's code would exclude these rows based on the even count)

what i need is to return any row if it has an unmatched pair (ie corresponding -ve value). ie suppress only if the negative is a duplicate.