Generating Report using ICEMAN !!!



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

Generating Report using ICEMAN !!!

Postby balaryan » Fri Mar 10, 2017 3:14 am

Hi Team,

I would like to hear from experts how to achieve the below-listed report with formatting.

Input file will be like field 1 with store #, field 2 with dept, field 3 with FLAG [y/n] and fourth field with status (A-Active or I-inactive)

|6003| 1|N|A|
|6003| 1|N|A|
|6003| 1|Y|A|
|6003| 1|N|A|
|6003| 2|Y|A|
|6003| 2|N|A|
|6003| 2|Y|A|
|6003| 2|N|A|
|6003| 3|N|A|
|6003| 3|Y|A|
|6003| 3|N|A|
|6003| 4|Y|A|
|6003| 4|N|A|
|6003| 5|Y|A|
|6003| 5|N|A|
|6003| 6|Y|A|
|6003| 6|N|A|
|6003| 6|Y|A|
|6003| 8|Y|A|

 


Output report


REPORT                         TOTAL REPORT               PGM-NAME
RUN DATE - 01/13/17                                               Page 1
 
Field#1     Field#2        Field#3                Field#4      Field#5
                                                                       
 6003          001         SUB-DIV1                 Y             15  
               001         SUB-DIV1                 N          1,058  
               002         SUB-DIV2                 Y          2,526  
               002         SUB-DIV2                 N          2,689  
               003         SUB-DIV3                 Y            130  
               003         SUB-DIV3                 N          1,283  
               004         SUB-DIV4                 N          2,652  
               005         SUB-DIV5                 N          1,920  
               006         SUB-DIV6                 Y            269  
               006         SUB-DIV6                 N          3,740  
               008         SUB-DIV8                 N          3,061  

               
                TOTAL STORE 6003   INBOUND ITEMS     7,789
                TOTAL STORE 6003   OUTBOUND ITEMS    1,190
                TOTAL STORE 6003   ITEMS             8,979
     
...
...
...
.
     
INVENTORY GRAND TOTAL           =                      38,415,086  
INVENTORY INBOUND GRAND TOTAL   =                      12,853,600  
INVENTORY OUTBOUND GRAND TOTAL  =                      25,561,486    


Input file contains millions of records in which field 1 hold several other store # records. In order to achieve the desired result, I will use DFSORT "SUM" (third field from the input file) for every dept under store # to get the records counts based on FLAG [Y/N]. Also how to display the store # for the very first record in the report. How to generate that report by grouping the field # 1(store), field #2 (dept) for every flag value (field #3).

Appreciate any hints. Thanks for your time.
balaryan
 
Posts: 11
Joined: Mon Jun 04, 2012 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generating Report using ICEMAN !!!

 

Re: Generating Report using ICEMAN !!!

Postby BillyBoyo » Fri Mar 10, 2017 5:04 am

Don't use SUM. The sample data you have shown is in sequence. You don't want to SORT millions of records just to be able to use SUM.

Use OUTFIL reporting procedures. There are good examples in DFSORT Getting Started, and the full details in the DFSORT Application Programming Guide.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 264 times

Re: Generating Report using ICEMAN !!!

Postby Aki88 » Fri Mar 10, 2017 9:51 am

Hello,

You can also look at the tools provided by ICETOOL's DISPLAY operator. It comes with a good suite of commands which can be used to format and present data.
Aki88
 
Posts: 369
Joined: Tue Jan 28, 2014 1:52 pm
Has thanked: 33 times
Been thanked: 35 times

Re: Generating Report using ICEMAN !!!

Postby balaryan » Fri Mar 10, 2017 6:30 pm

@BillyBoyo , The input file records will be in sequence order. Thanks Ai88 and billy. I will check on both OUTFIL as well ICETOOL.
balaryan
 
Posts: 11
Joined: Mon Jun 04, 2012 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generating Report using ICEMAN !!!

Postby balaryan » Fri Mar 10, 2017 11:46 pm

Hi All,

I tried with DFSort and got the required output.

SORT FIELDS=(3,7,CH,A,64,01,CH,A)
OUTFIL REMOVECC,NODETAIL,
SECTIONS=(64,1,TRAILER3=(3,8,64,1,X,COUNT=(M10,LENGTH=6)))

6003| 1|N  30588
6003| 1|Y   4262
6003| 2|N  48284
6003| 2|Y  62343
6003| 4|N   9967
6003| 4|Y      8
6003| 6|N   7590
6003| 6|Y    285
6003| 8|N   2864
6003| 8|Y     77
6003| 9|N  18538
6003| 9|Y    410


Is there any option to display the value 6003 for first record alone and rest should be filled with blanks. Also I struck with displaying sub-total and total count. Already I am using TRAILER3 in getting the field 4 (in output report). If I use TRAILER1 to display sub total or total, I am getting the different Value.

Please do advice how to proceed on this.
balaryan
 
Posts: 11
Joined: Mon Jun 04, 2012 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generating Report using ICEMAN !!!

Postby balaryan » Sat Mar 11, 2017 2:59 am

*** Latest updates ***

I got the required report with final GRAND TOTAL. Still digging out to get the sub-total for every store. I am trying to use TRAILER2 to achieve it. But it is not working out.

 SORT FIELDS=(3,7,CH,A,64,01,CH,A)                        
  OUTFIL REMOVECC,NODETAIL,                                
  SECTIONS=(64,1,TRAILER3=(3,8,11:64,1,12:'|',            
                   X,COUNT=(M10,LENGTH=6))),              
  TRAILER1=(2/,6:'TOTAL COUNT OF THE FILE IS:', 2/,        
                 COUNT=(EDIT=(II,III,III,IIT),LENGTH=14))  
/*  



My output is

6084|28|  Y|     42                            
6087| 1|  N|  30493                            
6087| 1|  Y|   4472                            
6087| 2|  N|  49183                            
6087| 2|  Y|  61542                            
6087| 3|  N|   1029                            
6087| 3|  Y|     26                            
6087| 4|  N|   8926                            
6087| 4|  Y|      8                            
6087| 8|  N|  10793                            
6087| 8|  Y|     77                            
6087| 9|  N|  18547                            
6087| 9|  Y|    410                            
6087|11|  N|   4374                            
6087|11|  Y|      5                            
6087|12|  N|    474                            
6087|12|  Y|    836                            
6087|17|  N|   2585                            
6087|17|  Y|     54                            
6087|21|  N|   1965                            
6087|21|  Y|      9                            
6087|28|  N|   5498                            
6087|28|  Y|     41                            
                                               
     TOTAL COUNT OF THE FILE IS:     6,384,656


But expected output is


6087|001|  N|  30493                            
    |001|  Y|   4472                            
    |002|  N|  49183                            
    |002|  Y|  61542                            
    |003|  N|   1029                            
    |003|  Y|     26                            
    |004|  N|   8926                            
    |004|  Y|      8                            
    |008|  N|  10793                            
    |008|  Y|     77                            
    |009|  N|  18547                            
    |009|  Y|    410                            
    |011|  N|   4374                            
    |011|  Y|      5                            
    |012|  N|    474                            
    |012|  Y|    836                            
    |017|  N|   2585                            
    |017|  Y|     54                            
    |021|  N|   1965                            
    |021|  Y|      9                            
    |028|  N|   5498                            
    |028|  Y|     41                            
    SUBTOTAL COUNT of 6087 IS :       201,347   
   
   
    TOTAL COUNT OF THE FILE IS:     6,384,656
     


output Field#1 should display the value for very first record. Field#2 with three-byte length and filled with prefix zeros. Any help on this would be great. thanks.
balaryan
 
Posts: 11
Joined: Mon Jun 04, 2012 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generating Report using DFSORT!!!

Postby balaryan » Mon Mar 13, 2017 10:00 pm

Hi,

Any hint on the current issue. Appreciate your time and help.
balaryan
 
Posts: 11
Joined: Mon Jun 04, 2012 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Generating Report using ICEMAN !!!

Postby enrico-sorichetti » Mon Mar 13, 2017 10:53 pm

Any hint on the current issue


DO NOT SOLICIT FOR REPLIES
people answer on their own time and free of charge
( ... when and if they feel like )

if You have time constraints a paid consultant will be happy ( for a proper fee )
to provide help according to Your time requirements
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2620
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 127 times

Re: Generating Report using ICEMAN !!!

Postby NicC » Tue Mar 14, 2017 2:48 am

Topic locked - posted on another forum.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2681
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 102 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post