Solved

I need to total negative numbers and positive numbers from the same field in a file using WRKQRY

Posted on 2006-10-24
7
578 Views
Last Modified: 2012-08-13
I have GL entries in a file using a field GLAMT.  GLAMT may contain a negative or positive number for each record stored in the file. Is there a way using 'Define Result Fields' to create total fields based on whether the amount field data is positive or negative.
I.E. create a field called DEBITS totaling all amounts that are positive in the GLAMT field. create a field called CREDITS totalling all amounts that are negative in the GLAMT field.

I need this pretty quick.

Thanks,

Phil
0
Comment
Question by:pipster1
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Accepted Solution

by:
_b_h earned 500 total points
ID: 17798353
Hi Phil

Create query 1 to total the amounts where GLAMT is less than 0, output to a file outfile1
Create query 2 to total the amounts where GLAMT is not less than 0, output to a file outfile2
Create query 3 to join outfile1 and outfile2 together

You might be able to join the file to itself; I will try to test this method later to confirm that it works.
Join the file to itself as T01.file and T02.file
Select records T01.GLAMT *LT 0 OR T02.GLAMT *GE 0
then summarize on T01.GLAMT and T02.GLAMT

Post back any questions
Barry
0
 

Author Comment

by:pipster1
ID: 17798453
Sounds cool on the join to itself, I'll try that first and let u know
0
 
LVL 13

Expert Comment

by:_b_h
ID: 17798474
Phil
Found a system.
Second method does NOT work. I think the select would have to be done before the join to make it work.
Barry
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:pipster1
ID: 17798525
I guess there's no way to determine if a field is negative or not in result fields.
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17799022
hi
try
select sum(abs(GLAMT)) from myfile

Dave
0
 
LVL 14

Expert Comment

by:daveslater
ID: 17799090
Sorry just read the question.
Barry is almost there.
2 queries, the first select qty > 0 and simply creates the work field from GLAMT
, the second selects <=0 but multiplies GLAMT by -1 to make it +ve
the third now accumulates the values.
0
 

Author Comment

by:pipster1
ID: 17799210
You're right Dave, Barry's is the one.

Thanks to you both.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
We need a new way to communicate time sensitive or critical info.   The best part of my role at xMatters is visiting our clients all over the world to learn about how they operate their businesses, share insights that xMatters has gleaned across…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now