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
593 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Check out this step-by-step guide for asking an anonymous question on Experts Exchange.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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