ICETOOL to filter aggregate and generate CSV report



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

ICETOOL to filter aggregate and generate CSV report

Postby himanshupant » 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
himanshupant
 
Posts: 3
Joined: Wed Jul 21, 2010 8:50 am
Has thanked: 0 time
Been thanked: 0 time

Re: ICETOOL to filter aggregate and generate CSV report

Postby dick scherrer » 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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: ICETOOL to filter aggregate and generate CSV report

Postby himanshupant » Wed Jul 21, 2010 11:08 am

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

i/p data set

----+----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
himanshupant
 
Posts: 3
Joined: Wed Jul 21, 2010 8:50 am
Has thanked: 0 time
Been thanked: 0 time

Re: ICETOOL to filter aggregate and generate CSV report

Postby Frank Yaeger » 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?
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: ICETOOL to filter aggregate and generate CSV report

Postby himanshupant » 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...
himanshupant
 
Posts: 3
Joined: Wed Jul 21, 2010 8:50 am
Has thanked: 0 time
Been thanked: 0 time

Re: ICETOOL to filter aggregate and generate CSV report

Postby Frank Yaeger » Thu Jul 22, 2010 2:38 am

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

//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',')))
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post