Focal Point
[SOLVED]Filtering on two total fields

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3097050695

December 29, 2010, 10:31 AM
moyer1dl
[SOLVED]Filtering on two total fields
Hello

I am trying to develop a report that reports

by payee
by user
by trxamount

the problem is one of the contidions I am filtering on is where total cnt.trxamount is greater than one. The other is where total cnt.payeename is eq to 1. How do I do both without losing the detail of the trxamounts for each payee.

I have been able to make seperate file by summing on the user field and also in the second file just counting the trxamouont. Is there a way to do it in a single pass without having to match or try to join the files back up?

Thanks
Dan

This message has been edited. Last edited by: moyer1dl,


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
December 29, 2010, 11:07 AM
moyer1dl
I think I solved it. I was able to use rank on the payee field and then do a lst.rank and do a where total on that field.


IBM Main Frame: MVS, FIX, VSAM

Windows SQL

WF 7.7
December 29, 2010, 11:13 AM
Dan Satchell
A multi-verb request might also work - something like this:

TABLE FILE <filename>
 SUM COMPUTE CNT_TRXAMOUNT/I9C = CNT.TRXAMOUNT ;
     COMPUTE CNT_PAYEENAME/I9C = CNT.PAYEENAME ;
 BY PAYEE
 BY USER (?)
-*
 PRINT <fieldnames>
 BY PAYEE
 BY USER
 BY TRXAMOUNT
 WHERE TOTAL CNT_TRXAMOUNT GT 1 ;
 WHERE TOTAL CNT_PAYEENAME EQ 1 ;
END



WebFOCUS 7.7.05