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
584 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 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