Creating 2 report from a PS file



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

Re: Creating 2 report from a PS file

Postby skolusu » Tue May 19, 2009 2:19 am

sudijana wrote:Now my requirement needs me to display the date even if no records are present corresponding to that date. :( Can you please tell me a workaround for this?

P.S. the batch job runs every Sunday(e.g. 08/03/09) and creates data for the previous week i.e Saturday(28/02/09) till Friday(06/03/09).

sudijana,

Use the following control cards. You can run the job on any day you want. It will still pick the date values from the previous week(sat thru friday).

//SYSIN    DD *                                                       
  INREC IFTHEN=(WHEN=INIT,                                             
  BUILD=(114C'0',48X,1,80,SEQNUM,1,ZD,RESTART=(1,3))),                 
  IFTHEN=(WHEN=(243,1,ZD,EQ,1),OVERLAY=(001:167,7,043:175,12)),       
  IFTHEN=(WHEN=(243,1,ZD,EQ,2),OVERLAY=(008:167,7,055:175,12)),       
  IFTHEN=(WHEN=(243,1,ZD,EQ,3),OVERLAY=(015:167,7,067:175,12)),       
  IFTHEN=(WHEN=(243,1,ZD,EQ,4),OVERLAY=(022:167,7,079:175,12)),       
  IFTHEN=(WHEN=(243,1,ZD,EQ,5),OVERLAY=(029:167,7,091:175,12)),       
  IFTHEN=(WHEN=(243,1,ZD,EQ,6),OVERLAY=(036:167,7,103:175,12))         
                                                                       
  SORT FIELDS=(163,3,CH,A),EQUALS                                     
                                                                       
  SUM FIELDS=(001,07,008,07,015,07,022,07,029,07,036,07,               
              043,12,055,12,067,12,079,12,091,12,103,12),FORMAT=ZD     
                                                                       
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(244:WKDAY)),                       
  IFTHEN=(WHEN=(244,3,CH,EQ,C'SUN'),                                   
  OVERLAY=(115:DATE1-08,DATE1-06,DATE1-05,DATE1-04,DATE1-3,DATE1-2)), 
  IFTHEN=(WHEN=(244,3,CH,EQ,C'MON'),                                   
  OVERLAY=(115:DATE1-09,DATE1-07,DATE1-06,DATE1-05,DATE1-4,DATE1-3)), 
  IFTHEN=(WHEN=(244,3,CH,EQ,C'TUE'),                                   
  OVERLAY=(115:DATE1-10,DATE1-08,DATE1-07,DATE1-06,DATE1-5,DATE1-4)), 
  IFTHEN=(WHEN=(244,3,CH,EQ,C'WED'),                                   
  OVERLAY=(115:DATE1-11,DATE1-09,DATE1-08,DATE1-07,DATE1-6,DATE1-5)), 
  IFTHEN=(WHEN=(244,3,CH,EQ,C'THU'),                                   
  OVERLAY=(115:DATE1-12,DATE1-10,DATE1-09,DATE1-08,DATE1-7,DATE1-6)), 
  IFTHEN=(WHEN=(244,3,CH,EQ,C'FRI'),                                   
  OVERLAY=(115:DATE1-13,DATE1-11,DATE1-10,DATE1-09,DATE1-8,DATE1-7)), 
  IFTHEN=(WHEN=(244,3,CH,EQ,C'SAT'),                                   
  OVERLAY=(115:DATE1-14,DATE1-12,DATE1-11,DATE1-10,DATE1-9,DATE1-8))   
                                                                       
  OUTFIL FNAMES=OUT1,REMOVECC,                                         
  BUILD=(163,3,5X,                                                     
         001,07,ZD,M10,LENGTH=7,2X,08,07,ZD,M10,LENGTH=7,2X,           
         015,07,ZD,M10,LENGTH=7,2X,22,07,ZD,M10,LENGTH=7,2X,           
         029,07,ZD,M10,LENGTH=7,2X,36,07,ZD,M10,LENGTH=7,2X,           
         167,07,ZD,M10,LENGTH=7,80:X),                                 
  HEADER1=(10:' SALES INVOICE RECORDS BY DAY(RECORDS/DIV/DAY)',/,       
           10:' *********************************************',/,/,     
           01:'DIV',4X,119,4,115,4,X,127,4,123,4,X,135,4,131,4,X,       
              143,4,139,4,X,151,4,147,4,X,159,4,155,4,4X,'TOTAL',/,     
           01:3'*',4X,8'*',X,8'*',X,8'*',X,8'*',X,8'*',X,8'*',X,8'*'), 
  TRAILER1=(/,'TOTAL :',TOT=(001,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(008,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(015,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(022,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(029,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(036,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(167,07,ZD,M10,LENGTH=8))                   
                                                                       
  OUTFIL FNAMES=OUT2,REMOVECC,                                         
  BUILD=(163,3,4X,                                                     
         043,12,ZD,M10,LENGTH=12,X,055,12,ZD,M10,LENGTH=12,X,           
         067,12,ZD,M10,LENGTH=12,X,079,12,ZD,M10,LENGTH=12,X,           
         091,12,ZD,M10,LENGTH=12,X,103,12,ZD,M10,LENGTH=12,X,           
         175,12,ZD,M10,LENGTH=12,104:X),                               
  HEADER1=(30:' SALES $ BY DAY(AMOUNT/DIV/DAY)',/,                     
           30:' ******************************',/,/,                   
           01:'DIV',8X,119,4,115,4,5X,127,4,123,4,5X,135,4,131,4,5X,   
              143,4,139,4,5X,151,4,147,4,5X,159,4,155,4,8X,'TOTAL',/,   
           01:3'*',8X,8'*',5X,8'*',5X,8'*',5X,8'*',5X,                 
              8'*',5X,8'*',5X,8'*'),                                   
  TRAILER1=(/,'TOTAL :',TOT=(043,12,ZD,M10,LENGTH=12),X,               
                        TOT=(055,12,ZD,M10,LENGTH=12),X,               
                        TOT=(067,12,ZD,M10,LENGTH=12),X,               
                        TOT=(079,12,ZD,M10,LENGTH=12),X,               
                        TOT=(091,12,ZD,M10,LENGTH=12),X,               
                        TOT=(103,12,ZD,M10,LENGTH=12),X,               
                        TOT=(175,12,ZD,M10,LENGTH=12))                 
/*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Creating 2 report from a PS file

Postby sudijana » Tue May 19, 2009 5:40 pm

Hi skolusu,

Please check the syntax error: http://picasaweb.google.com/sudiptojana ... 4020216258
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Creating 2 report from a PS file

Postby sudijana » Tue May 19, 2009 5:53 pm

skolusu,

I have run your first code and getting some data error.

Please check my i/p and o/p

i/p:
003,0004093,000000617013.17,05092009
003,0001991,000000271742.17,05112009
003,0006621,000001028847.17,05122009
003,0005513,000000908478.02,05132009
003,0005089,000000775129.56,05142009
003,0005179,000001017333.24,05152009
004,0023008,000004920745.83,05112009
004,0022656,000006061571.39,05122009
004,0018608,000004779798.63,05132009
004,0016880,000005300860.45,05142009
004,0019500,000006023861.49,05152009

o/p:

DIV 05092009 05112009 05122009 05132009 05142009 05152009 TOTAL
*** ******** ******** ******** ******** ******** ******** ********
003 617013 271742 1028847 908478 775129 1017333 4618542
004 4920745 6061571 4779798 5300860 6023861 0 27086835

In the i/p file for Div 004 on 05092009 there is no record, where as in o/p file its showing 4920745 which is actually on 05112009. All the data has been shifted to one day before and the last day 05152009 is showing 0.

This is being repeated for all the divisions which has no record for a day. I am not sure if there is no record for 2 or 3 days for a div how the output will look like..
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Creating 2 report from a PS file

Postby skolusu » Tue May 19, 2009 11:06 pm

sudijana wrote:In the i/p file for Div 004 on 05092009 there is no record, where as in o/p file its showing 4920745 which is actually on 05112009. All the data has been shifted to one day before and the last day 05152009 is showing 0.

This is being repeated for all the divisions which has no record for a day. I am not sure if there is no record for 2 or 3 days for a div how the output will look like..


*Sigh* You need to remember that we are communicating via web and I really can't see your real data. Whatever you present here are taken and certain assumptions are made and a solution is provided to the best of our ability. Your initial posts never mentioned all the rules for getting the data. Based on your posts I assumed that your data is sorted on the Division and date and it has 6 days worth of data for each division. Now you are telling me that you don't have data for some days.

well here is the solution which I think will cover all your rules. The following Job validates the data against the date we get from the system and put the values accordingly.

//SYSIN    DD *                                                         
  INREC IFTHEN=(WHEN=INIT,BUILD=(162X,1,28,33,4,29,4,WKDAY)),           
  IFTHEN=(WHEN=(199,3,CH,EQ,C'SUN'),                                   
  OVERLAY=(115:DATE1-08,DATE1-06,DATE1-05,DATE1-04,DATE1-3,DATE1-2)),   
  IFTHEN=(WHEN=(199,3,CH,EQ,C'MON'),                                   
  OVERLAY=(115:DATE1-09,DATE1-07,DATE1-06,DATE1-05,DATE1-4,DATE1-3)),   
  IFTHEN=(WHEN=(199,3,CH,EQ,C'TUE'),                                   
  OVERLAY=(115:DATE1-10,DATE1-08,DATE1-07,DATE1-06,DATE1-5,DATE1-4)),   
  IFTHEN=(WHEN=(199,3,CH,EQ,C'WED'),                                   
  OVERLAY=(115:DATE1-11,DATE1-09,DATE1-08,DATE1-07,DATE1-6,DATE1-5)),   
  IFTHEN=(WHEN=(199,3,CH,EQ,C'THU'),                                   
  OVERLAY=(115:DATE1-12,DATE1-10,DATE1-09,DATE1-08,DATE1-7,DATE1-6)),   
  IFTHEN=(WHEN=(199,3,CH,EQ,C'FRI'),                                   
  OVERLAY=(115:DATE1-13,DATE1-11,DATE1-10,DATE1-09,DATE1-8,DATE1-7)),   
  IFTHEN=(WHEN=(199,3,CH,EQ,C'SAT'),                                   
  OVERLAY=(115:DATE1-14,DATE1-12,DATE1-11,DATE1-10,DATE1-9,DATE1-8))   
  SORT FIELDS=(163,3,CH,A)                                             
                                                                       
  OUTREC IFTHEN=(WHEN=INIT,BUILD=(1,198)),                             
  IFTHEN=(WHEN=(115,8,CH,EQ,191,8,CH),OVERLAY=(01:167,7,043:175,12)),   
  IFTHEN=(WHEN=(123,8,CH,EQ,191,8,CH),OVERLAY=(08:167,7,055:175,12)),   
  IFTHEN=(WHEN=(131,8,CH,EQ,191,8,CH),OVERLAY=(15:167,7,067:175,12)),   
  IFTHEN=(WHEN=(139,8,CH,EQ,191,8,CH),OVERLAY=(22:167,7,079:175,12)),   
  IFTHEN=(WHEN=(147,8,CH,EQ,191,8,CH),OVERLAY=(29:167,7,091:175,12)),   
  IFTHEN=(WHEN=(155,8,CH,EQ,191,8,CH),OVERLAY=(36:167,7,103:175,12))   
                                                                       
  OUTFIL FNAMES=OUT1,REMOVECC,NODETAIL,BUILD=(80X),                     
  HEADER1=(10:' SALES INVOICE RECORDS BY DAY(RECORDS/DIV/DAY)',/,       
           10:' FOR WEEK BEGIN ON ',119,4,115,4,                       
              ' & END ON ',159,4,155,4,/,                               
           10:' *********************************************',/,/,     
           01:'DIV',4X,119,4,115,4,X,127,4,123,4,X,135,4,131,4,X,       
              143,4,139,4,X,151,4,147,4,X,159,4,155,4,4X,'TOTAL',/,     
           01:3'*',4X,8'*',X,8'*',X,8'*',X,8'*',X,8'*',X,8'*',X,8'*'), 
  SECTIONS=(163,3,                                                     
  TRAILER3=(163,3,5X,                                                   
            TOT=(001,07,UFF,M10,LENGTH=7),2X,                           
            TOT=(008,07,UFF,M10,LENGTH=7),2X,                           
            TOT=(015,07,UFF,M10,LENGTH=7),2X,                           
            TOT=(022,07,UFF,M10,LENGTH=7),2X,                           
            TOT=(029,07,UFF,M10,LENGTH=7),2X,                           
            TOT=(036,07,UFF,M10,LENGTH=7),2X,                           
            TOT=(167,07,UFF,M10,LENGTH=7))),                           
  TRAILER1=(/,'TOTAL :',TOT=(001,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(008,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(015,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(022,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(029,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(036,07,ZD,M10,LENGTH=8),X,                 
                        TOT=(167,07,ZD,M10,LENGTH=8))                   
                                                                       
  OUTFIL FNAMES=OUT2,REMOVECC,NODETAIL,BUILD=(104X),                 
  HEADER1=(37:' SALES $ BY DAY(AMOUNT/DIV/DAY)',/,                   
           30:' FOR WEEK BEGIN ON ',119,4,115,4,                     
              ' & END ON ',159,4,155,4,/,                           
           30:' *********************************************',/,/, 
           01:'DIV',8X,119,4,115,4,5X,127,4,123,4,5X,135,4,131,4,5X,
              143,4,139,4,5X,151,4,147,4,5X,159,4,155,4,8X,'TOTAL',/,
           01:3'*',8X,8'*',5X,8'*',5X,8'*',5X,8'*',5X,               
              8'*',5X,8'*',5X,8'*'),                                 
  SECTIONS=(163,3,                                                   
  TRAILER3=(163,3,4X,                                               
            TOT=(043,12,UFF,M10,LENGTH=12),X,                       
            TOT=(055,12,UFF,M10,LENGTH=12),X,                       
            TOT=(067,12,UFF,M10,LENGTH=12),X,                       
            TOT=(079,12,UFF,M10,LENGTH=12),X,                       
            TOT=(091,12,UFF,M10,LENGTH=12),X,                       
            TOT=(103,12,UFF,M10,LENGTH=12),X,                       
            TOT=(175,12,UFF,M10,LENGTH=12))),                       
  TRAILER1=(/,'TOTAL :',TOT=(043,12,ZD,M10,LENGTH=12),X,             
                        TOT=(055,12,ZD,M10,LENGTH=12),X,             
                        TOT=(067,12,ZD,M10,LENGTH=12),X,             
                        TOT=(079,12,ZD,M10,LENGTH=12),X,             
                        TOT=(091,12,ZD,M10,LENGTH=12),X,             
                        TOT=(103,12,ZD,M10,LENGTH=12),X,             
                        TOT=(175,12,ZD,M10,LENGTH=12))               
/*
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Creating 2 report from a PS file

Postby skolusu » Wed May 20, 2009 6:49 am

oops I forgot to add the Symbols DD which picks the day of the week from the system. Add it after the pgm=sort like I have shown below

//STEP0100 EXEC PGM=SORT
//SYMNAMES DD *         
WKDAY,S'&LWDAY'         
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: Creating 2 report from a PS file

Postby sudijana » Thu Aug 20, 2009 12:45 pm

Hi skolusu,

The syntax worked fine until there is some other week's data in the file.
Lets say for example.
**i/p file**
003,0004195,000000520973.62,07272009
003,0004814,000000842755.24,07282009
003,0005601,000000880337.37,07292009
003,0004964,000000675301.68,07302009
003,0004539,000000668486.30,07312009
004,0022767,000004853684.03,07272009
004,0022173,000005901185.36,07282009
004,0019354,000004448030.35,07292009
004,0017257,000004560111.04,07302009
004,0019114,000004519782.42,07312009

**o/p file**
********************************* Top of Data *************************
SALES INVOICE RECORDS BY DAY(RECORDS/DIV/DAY)
FOR WEEK BEGIN ON 08082009 & END ON 08142009
*********************************************

DIV 08082009 08102009 08112009 08122009 08132009 08142009 TOTAL
*** ******** ******** ******** ******** ******** ******** ********
003 0 0 0 0 0 0 24113
004 0 0 0 0 0 0 100665
008 0 0 0 0 0 0 285402
010 0 0 0 0 0 0 231386
012 0 0 0 0 0 0 127736
017 0 0 0 0 0 0 78896

As it is checking only the previous date, so no data is found.. But we need to make the sysin cond flexible so that we can run our job for any week(Saturday-Friday). Pls help
sudijana
 
Posts: 9
Joined: Thu May 14, 2009 7:48 pm
Has thanked: 0 time
Been thanked: 0 time

Previous

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post