Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to count only if specfic field is present in flat file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
MOHAN KUMAR DURAIRAJAN

New User


Joined: 16 Apr 2012
Posts: 11
Location: INDIA

PostPosted: Wed Jan 09, 2013 4:38 pm    Post subject: How to count only if specfic field is present in flat file
Reply with quote

Hi Everyone.

I need your help.

Requirement:

1. Based on two fields "A" and "B",
i need produce sum of 'X' field and need to produce the count of 'Y' field (Condition for count : only if specific value is present in that position).

2. Need to write it to output with A,B, sum of X and Y and count of Y in Z


Eg:
A - CHAR
B - CHAR
X - PS
Y - CHAR

Input :
Code:
A     B     X      Y
****  ****  ****   ***             
1220  1301  00.11   PO
1220  1301  00.11   FF
1220  1301  00.11   FF
1220  1301  00.11   FF
1220  1301  00.11   PO

1220  1302  00.11   PO
1220  1302  00.11   FF
1220  1302  00.11   FF
1220  1302  00.11   PO

1221  1301  00.11   PO
1221  1301  00.11   FF
1221  1301  00.11   PO
1221  1301  00.11   PO


EXPECTED OUTPUT:
Code:
A     B     X      Y       Z
****  ****  ****   ***     **
1220  1301  00.55   PO     2
1220  1302  00.44   PO     2
1221  1301  00.44   PO     3


Code'd and aligned
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jan 09, 2013 4:50 pm    Post subject:
Reply with quote

Code:
1220 1301 00.55 PO 2
1220 1302 00.44 PO 2
1221 1301 00.44 APO 3


Looking at your output I see for each A & B combination you sum even Y=FF records? but have the value of Y as PO

But looking at the last records how come you get APO? Or is it typo?

Edit:Moderators my question is being answered by correcting TS post

probably this could be deleted
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Jan 09, 2013 4:52 pm    Post subject: Reply to: How to count only if specfic field is present in f
Reply with quote

Please use the Code tags and the Preview button to align data.

Your data is in sorted order. You only need the POs. You want to know how many POs per key of A and B and you also want the sum of X.

Is that about it?
Back to top
View user's profile Send private message
MOHAN KUMAR DURAIRAJAN

New User


Joined: 16 Apr 2012
Posts: 11
Location: INDIA

PostPosted: Wed Jan 09, 2013 5:57 pm    Post subject:
Reply with quote

for each A & B combination you sum even Y=FF records ==> yes
at the last records how come you get APO? Or is it typo? ==> typo

You want to know how many POs per key of A and B and you also want the sum of X. ==> yes
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jan 09, 2013 6:20 pm    Post subject:
Reply with quote

For each group of A and B combination does Y always start with PO??
Back to top
View user's profile Send private message
MOHAN KUMAR DURAIRAJAN

New User


Joined: 16 Apr 2012
Posts: 11
Location: INDIA

PostPosted: Wed Jan 09, 2013 6:27 pm    Post subject:
Reply with quote

Hi Pandora,

For each group of A and B combination does Y always start with PO??

It can PO or FF.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jan 09, 2013 6:47 pm    Post subject:
Reply with quote

Try this

Code:
   INREC IFTHEN=(WHEN=INIT,OVERLAY=(13:13,5,UFF,ZD,LENGTH=4)),
         IFTHEN=(WHEN=(21,2,CH,EQ,C'PO'),BUILD=(1:1,30,C'1')),
         IFTHEN=(WHEN=(21,2,CH,EQ,C'FF'),BUILD=(1:1,30,C'0'))
   SORT FIELDS=(1,12,CH,A)
   SUM FIELDS=(13,5,ZD,31,1,ZD)
   OUTREC OVERLAY=(13:13,4,ZD,EDIT=(TT.TT),21:C'PO')


You might need to modify it slightly for your need
Back to top
View user's profile Send private message
MOHAN KUMAR DURAIRAJAN

New User


Joined: 16 Apr 2012
Posts: 11
Location: INDIA

PostPosted: Wed Jan 09, 2013 8:21 pm    Post subject:
Reply with quote

Hi Pandora,

Your logic worked fine well. Thank you.
But still am facing one issue.

Suppose Input:
Code:

A           B     X        Y
****  ****  ****   ***             
1220  1301  00.11   FF
1220  1301  00.11   PO
1220  1301  00.11   FF
1220  1301  00.11   FF
1220  1301  00.11   PO

1221  1301  00.11   FF


Getting Output :
Code:
1220  1301  00.55   PO   2
1221  1301  00.11   PO   0   


as you have given outrec : 21:C'PO', its changing the output at Y field as PO . But If there lacks of records with only FF, am giving count as 0 with field Y as 'PO'.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Wed Jan 09, 2013 8:33 pm    Post subject:
Reply with quote

So for a group of A and B combination when there is no 'PO' what would you expect in output ??

Also when you say lakhs (lacks means something else) of record with PO please be aware that you might need to change C'0' OR C'1' to C'00000001' or so
Back to top
View user's profile Send private message
Anbarasan D

New User


Joined: 20 Apr 2010
Posts: 21
Location: USA

PostPosted: Thu Jan 10, 2013 2:19 am    Post subject:
Reply with quote

Hi Pandora,

I am thinking we wont get desire output for the following input.

Code:
1220  1301  00.33   FF
1220  1301  00.33   PO
1220  1301  00.33   FF
1220  1301  00.33   FF
1220  1301  00.33   PO


I dont have mainframe access today.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu Jan 10, 2013 6:39 am    Post subject: Reply to: How to count only if specfic field is present in f
Reply with quote

Anbarasan D,

You haven't answered Pandora-Box's last question. To put it another way, what are the rules for Y in the output?
Back to top
View user's profile Send private message
Anbarasan D

New User


Joined: 20 Apr 2010
Posts: 21
Location: USA

PostPosted: Thu Jan 10, 2013 7:38 am    Post subject:
Reply with quote

Hi Bill,

I havent open this question. I used to see all the question and try to answer whichever i can.

When i was seeing Pandora answer i thought the sum will show incorrect value if sum value goes beyond "1.00". Today i dont have access to mainframe to test that.
Back to top
View user's profile Send private message
MOHAN KUMAR DURAIRAJAN

New User


Joined: 16 Apr 2012
Posts: 11
Location: INDIA

PostPosted: Thu Jan 10, 2013 11:31 am    Post subject:
Reply with quote

Hi Pandora,

Thanks. Your code helped me alot.
So for a group of A and B combination when there is no 'PO' what would you expect in output ?? ==> Fine. PO as 0 if no PO found.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jan 10, 2013 11:32 am    Post subject:
Reply with quote

Anbarasan,


The Card is very much ok for the input you provided

For input which would produce output of three digits you could use something like this below by increasing the length

Code:
INREC IFTHEN=(WHEN=INIT,OVERLAY=(13:13,5,UFF,ZD,LENGTH=6)),
      IFTHEN=(WHEN=(21,2,CH,EQ,C'PO'),BUILD=(1:1,30,C'01')),
      IFTHEN=(WHEN=(21,2,CH,EQ,C'FF'),BUILD=(1:1,30,C'00'))
SORT FIELDS=(1,12,CH,A)
SUM FIELDS=(13,6,ZD,31,2,ZD)
OUTREC OVERLAY=(13:13,6,ZD,EDIT=(TTTT.TT),21:C'PO')
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu Jan 10, 2013 1:31 pm    Post subject:
Reply with quote

Anbarasan D wrote:
Hi Bill,

I havent open this question. I used to see all the question and try to answer whichever i can.

When i was seeing Pandora answer i thought the sum will show incorrect value if sum value goes beyond "1.00". Today i dont have access to mainframe to test that.


Apologies, Anbarasan D, I wasn't paying enough attention.

You could still have a shot at the answer :-)

Meanwhile, the actual TS is... MOHAN KUMAR DURAIRAJAN, can you answer the question please?
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Thu Jan 10, 2013 2:55 pm    Post subject: Reply to: How to count only if specfic field is present in f
Reply with quote

And MOHAN KUMAR DURAIRAJAN is happily asking another question without attending to this...
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jan 10, 2013 3:37 pm    Post subject:
Reply with quote

Yes Bill I saw that long back

I am happy to help but he isnt happy to respond icon_cool.gif icon_sad.gif
Back to top
View user's profile Send private message
MOHAN KUMAR DURAIRAJAN

New User


Joined: 16 Apr 2012
Posts: 11
Location: INDIA

PostPosted: Thu Jan 10, 2013 6:31 pm    Post subject:
Reply with quote

Am sorry Pandora. Your code worked fine. Thanks alot for that.
And for Anbarasan question, if we increase the length of the field, it will calculate it correctly.
As Pandora clarified in previous chat, so i didn't reply back.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jan 10, 2013 8:26 pm    Post subject:
Reply with quote

Glad it worked for you icon_smile.gif

Note : When PO > 9 records then you might need to modify like my latest card provided
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Add PD field from 2nd file to PD in 1st Sushant Garje DFSORT/ICETOOL 6 Thu Dec 01, 2016 4:32 pm
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts CICS Roll back partially - Need to re... dwijadas CICS 4 Wed Nov 16, 2016 4:30 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us