Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
622 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 2000 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Take a look at these 6 Outlook Email management tools which can augment the working and performance of Microsoft Outlook to give you a more rewarding emailing experience.
Your business may be under attack from a silent enemy that is hard to detect. It works stealthily in the shadows to access and exploit your critical business information, sensitive confidential data and intellectual property, for commercial gain. T…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

916 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