×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help.. report to count rows based on criteria..

Help.. report to count rows based on criteria..

Help.. report to count rows based on criteria..

(OP)
I have a table like:

Table 1

POL-ID
EFF-DATE
TYP-IND


I need to generate a report like :

Week-Ending   Type1  Type2  Type3  Type4
06Jul07       20     10     5      7
13Jul07       15     17     12     4
20Jul07       18     19     1      12
27Jul07       5      10     11     20

Total         58     56     29     43

How can I do that?

RE: Help.. report to count rows based on criteria..

How about
DEFINE FILE TABLE1
TYPE1/I5 = IF TYP-IND EQ '1' THEN 1 ELSE 0;
TYPE2/I5 = IF TYP-IND EQ '2' THEN 1 ELSE 0;
TYPE3/I5 = IF TYP-IND EQ '3' THEN 1 ELSE 0;
TYPE4/I5 = IF TYP-IND EQ '4' THEN 1 ELSE 0;
END
TABLE FILE TABLE1
SUM TYPE1 TYPE2 TYPE3 TYPE4
BY EFF-DATE AS "WEEK ENDING"
etc

You may have to do some DEFINE with the EFF-DATE to control the SORT.
HTH

RE: Help.. report to count rows based on criteria..

Also try
TABLE FILE TABLE1
SUM CNT.POLL_ID
ACROSS TYPE_IND
BY EFF_DATE
END

RE: Help.. report to count rows based on criteria..

(OP)
Thanks gizzy17. way to go.. I have another question.

How to joint more than two hold files together to get a report?

Use the above sample, I need to generate another report like:

Week-Ending   Type1  Type2  Type3  Type4   Received
06Jul07       20     10     5      7       56
13Jul07       15     17     12     4       65
20Jul07       18     19     1      12      45
27Jul07       5      10     11     20      20

Total         58     56     29     43      181

For the column of Received, I can get from another hold file, it has same rows as the first hold file...

RE: Help.. report to count rows based on criteria..

Putting the Received column on the end would be the difficult part.  For this, the easiest solution would be to create a field for each type as jimster had pointed out.

If recieved could be before the across columns then you would just need to join the tables together.

The syntax for the join command is
JOIN keyfld [AND keyfld2 ...] IN hosttable TO [ALL] keyfld [AND keyfld2] IN childtable [AS joinname]
END
The "ALL" keyword would be used if you need a 1-n relationship.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close