IBM Mainframe Forum

Posted: **Wed Jul 21, 2010 9:09 am**

Hello

Apologies if my doubt is a basic from DFSORT / ICETOOL point of view but I was not able to find it anywhere so thought of asking in this expert forum.

I have an i/p file of FB 1000 LRECL and I want to make a csv file out of this ( say of 100 byte LRECL). The i/p file has lot of fields and I want to extract only a few fields out of this in my output csv file(to be finally uploaded to an excel file). The input file contains a list of all customer accounts with fields like branch number,account number ( both in packed format) , account type( pic XX) , cleared balance(S9(8)V99 COMP-3) and a lot of other fields. The i/p file is sorted on basis of branch number and account number.

In the output file I want to aggregate the records on the basis of account type. So the output I want is something like

account type,description,cleared balance
A1,Savings account,xxxxxxxxx.xx
A2,Current account,yyyyyyyyyyy.yy
.
.
.
.
.
so on and so forth
So the above record is a total of cleared balances of all accounts belonging to account type A1. The description could be hardcoded ( if ICETOOL/SORT allows that ) . Cleared balance xxxxxxxxx.xx is the total of all cleared balance of all account belonging to A1 type

Posted: **Wed Jul 21, 2010 9:41 am**

Hello and welcome to the forum,

Please post some sample input data (relevant fields only) and the output you want from that input. Mention any processing rules. You have already mentioned the dsorg and lrecl of files, but it would be helpful to repeat this with the complete info.

Posted: **Wed Jul 21, 2010 11:08 am**

Thanks.. Ok I will give a sample data set below..

i/p data set

Select all

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
MR AAA NEW DELHI INDIA A1 DONT CARE UPTO 1000 BYTES
011122222DD4CCC4DCE4CCDCC4CDCCC4CF00100CDDE4CCDC4EDED4FFFF4CEECE44444444
111C2222C49011105560453890954910110000C465303195047360100002835200000000
-----------------------------------------------------------------------
MR BBB NEW DELHI INDIA A2 DONT CARE UPTO 1000 BYTES
011133333DD4CCC4DCE4CCDCC4CDCCC4CF00100CDDE4CCDC4EDED4FFFF4CEECE44444444
111C3333C49022205560453890954910120000C465303195047360100002835200000000
-----------------------------------------------------------------------
Ã Ã Ã Ã <MR CCC NEW DELHI INDIA A1 DONT CARE UPTO 1000 BYTES
011144444DD4CCC4DCE4CCDCC4CDCCC4CF00200CDDE4CCDC4EDED4FFFF4CEECE44444444
111C4444C49033305560453890954910110000C465303195047360100002835200000000
-----------------------------------------------------------------------
ÃÃÃÃ*MR DDD NEW DELHI INDIA A1 & DONT CARE UPTO 1000 BYTES
011155555DD4CCC4DCE4CCDCC4CDCCC4CF00500CDDE4CCDC4EDED4FFFF4CEECE44444444
111C5555C49044405560453890954910110000D465303195047360100002835200000000
-----------------------------------------------------------------------
ÃƒÃƒÃƒÃƒ%MR EEE NEW DELHI INDIA A2 DONT CARE UPTO 1000 BYTES
011166666DD4CCC4DCE4CCDCC4CDCCC4CF00100CDDE4CCDC4EDED4FFFF4CEECE44444444
111C6666C49055505560453890954910120000C465303195047360100002835200000000

So for the above sample file the output should be like

A1,Savings account,-200.00
A2,Current account,200.00

The description Savings account , Current account can be hard coded in the sort CARD.Please let me know if any more information is needed

Posted: **Wed Jul 21, 2010 11:37 pm**

The description Savings account , Current account can be hard coded

Based on what? How do we know what description to use for what? How do we know that the A1 record should have "Savings account" for the description and the A2 record should have "Current account" for the description? What are the rules for determining what description to hardcode for which output record?

It appears that the account type field is 33,2,CH - is that correct?

It appears that the cleared balance field is 35,5,PD - is that correct?

Posted: **Thu Jul 22, 2010 1:23 am**

Sorry for the confusion.... I forgot to add that only 4 account types are possible

A1 Savings account
A2 Current account
A3 Loan account
A3 Mortgage account

We know this before hand so we could hard code this in the sort step (if SORT allows it)

You are right about the description of account type and cleared balance field...

Posted: **Thu Jul 22, 2010 2:38 am**

Here's a DFSORT job that will do what you asked for:

Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file
//SORTOUT DD DSN=... output file
//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,
BUILD=(33,2,C',',21:35,5,PD,TO=PD,LENGTH=5)),
IFTHEN=(WHEN=(1,2,CH,EQ,C'A1'),OVERLAY=(4:C'Savings account')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'A2'),OVERLAY=(4:C'Current account')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'A3'),OVERLAY=(4:C'Loan account')),
IFTHEN=(WHEN=(1,2,CH,EQ,C'A4'),OVERLAY=(4:C'Mortgage account'))
SORT FIELDS=(1,2,CH,A)
SUM FIELDS=(21,5,PD)
OUTREC IFTHEN=(WHEN=INIT,
OVERLAY=(21:21,5,PD,EDIT=(SIIIIIIT.TT),SIGNS=(,-))),
IFTHEN=(WHEN=INIT,OVERLAY=(15:15,17,SQZ=(SHIFT=LEFT,MID=C',')))
/*

IBM Mainframe Forum

ICETOOL to filter aggregate and generate CSV report

ICETOOL to filter aggregate and generate CSV report

Re: ICETOOL to filter aggregate and generate CSV report

Re: ICETOOL to filter aggregate and generate CSV report

Re: ICETOOL to filter aggregate and generate CSV report

Re: ICETOOL to filter aggregate and generate CSV report

Re: ICETOOL to filter aggregate and generate CSV report