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
 

 

Convert rows into columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
vinothsubramanian

New User


Joined: 01 Sep 2006
Posts: 39
Location: Chennai, India

PostPosted: Mon Mar 16, 2009 6:00 pm    Post subject: Convert rows into columns
Reply with quote

Hi,

Could you please help me in converting rows into columns for the below mentioned sample.

I/P File:

Code:

A 2008 100
A 2009 150
B 2008 050
C 2008 100
C 2009 075


Expected o/p file:

Code:

     A   B   C
2008 100 050 100
2009 150 000 075


Please note that input file is sorted based on column 1 and we will not be knowing what values will be available in column 1. The maximum distinct characters is limited to 3 but they are not known. i.e., it can A,B and C or D, E and F or A, B and D. It will be maximum three but not the same combination.

Kindly help me out in getting this solved.

Regards,
Ram.
Back to top
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Mon Mar 16, 2009 10:41 pm    Post subject:
Reply with quote

Quote:
I/P File:

A 2008 100
A 2009 150
B 2008 050
C 2008 100
C 2009 075

Expected o/p file:
A B C
2008 100 050 100
2009 150 000 075
vinothsubramanian,

Can you explain how you arrived at this output as your input has only 2 rows for 2009.
Back to top
View user's profile Send private message
vinothsubramanian

New User


Joined: 01 Sep 2006
Posts: 39
Location: Chennai, India

PostPosted: Mon Mar 16, 2009 10:47 pm    Post subject:
Reply with quote

Hi Arun,

We have 3 distinct variables and we are populating the values for each variable in columns. If value is not available for that variable we will have to populate it as zeroes.

Kindly let me know if you need more information.

Regards,
Ram.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2276
Location: @my desk

PostPosted: Mon Mar 16, 2009 11:26 pm    Post subject:
Reply with quote

Quote:
Kindly let me know if you need more information
vinothsubramanian,

Are you expecting a maxium of 3 duplicates for field2? Still I am not sure why the 'second' field has '000' and why not the 'third'.
It would be better if you post a much more realistic example showing all possible combinations of input and the expected output out of it.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Tue Mar 17, 2009 3:37 pm    Post subject:
Reply with quote

Arun,

As per my understanding, 2009 doesn't have any value for 'B'. Hence second field ("B") was assigned to zeroes and first and third fields were assigned with the values mentioned in the input.
Back to top
View user's profile Send private message
vinothsubramanian

New User


Joined: 01 Sep 2006
Posts: 39
Location: Chennai, India

PostPosted: Tue Mar 17, 2009 8:03 pm    Post subject:
Reply with quote

Hi,

I was able to write the code for converting.

Code:
//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//SORTIN   DD *                                                     
A 2008 100                                                         
A 2009 150                                                         
B 2008 050                                                         
C 2008 100                                                         
C 2009 075                                                         
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
 INREC IFTHEN=(WHEN=INIT,                                           
        OVERLAY=(31:SEQNUM,16,ZD,47:SEQNUM,16,ZD,RESTART=(1,1))),   
        IFTHEN=(WHEN=(47,16,ZD,EQ,1),                               
        OVERLAY=(63:SEQNUM,16,ZD),HIT=NEXT),                       
        IFTHEN=(WHEN=(47,16,ZD,NE,1),                               
    OVERLAY=(79:SEQNUM,16,ZD,63:31,16,ZD,SUB,                       
             79,16,ZD,M11,LENGTH=16),HIT=NEXT),                     
        IFTHEN=(WHEN=(63,16,ZD,EQ,1),                               
        OVERLAY=(1:3,10,11:6,3,14:2C' 000')),                       
        IFTHEN=(WHEN=(63,16,ZD,EQ,2),                               
        OVERLAY=(1:3,10,11:C'000 ',15:6,3,18:C' 000')),             
        IFTHEN=(WHEN=(63,16,ZD,EQ,3),                               
        OVERLAY=(1:3,10,11:2C'000 ',19:6,3))                       
  SORT FIELDS=(1,5,CH,A)                                           
  SUM FIELDS=(11,3,ZD,15,3,ZD,19,3,ZD)                             
  OUTREC FIELDS=(1:1,5,6:11,3,9:1X,10:15,3,13:1X,14:19,3)           


O/P:
Code:
2008 100 050 100
2009 150 000 075




However, I was not able to achieve the header. Can anyone please help me in getting it.

Regards,
Ram
Ps: I also learned to use Code tag in my post. icon_smile.gif icon_biggrin.gif
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Tue Mar 17, 2009 9:31 pm    Post subject:
Reply with quote

You can add the following DFSORT control statement to get the header (I used REMOVECC, assuming you didn't want ANSI carriage control characters).

Code:

  OUTFIL REMOVECC,                         
   HEADER1=(6:C'A',10:C'B',14:C'C')       
Back to top
View user's profile Send private message
vinothsubramanian

New User


Joined: 01 Sep 2006
Posts: 39
Location: Chennai, India

PostPosted: Tue Mar 17, 2009 9:46 pm    Post subject:
Reply with quote

Hi,

Thanks. But my issue is I will not be knowing whether these three characters will be A, B and C. It can be any three character between A-Z

Regards,
Ram.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Mar 17, 2009 10:20 pm    Post subject: Reply to: Convert rows into columns
Reply with quote

vinoth wrote:
But my issue is I will not be knowing whether these three characters will be A, B and C. It can be any three character between A-Z


Vinoth,

I am not sure as to how you would know which value is missing. Lets take this example data

Code:

A 2008 100     
A 2009 150     
B 2008 050     
C 2008 100     
C 2009 075     
Y 2010 300     
Z 2010 250     


Now for 2008 you have 3 value which can be grouped as a single record and for 2009 you have a missing 'b' record there is no way to identify if the missing record is an 'a' ,'b' or 'c'

for 2010 you have completely different set of alphabets in pos 1 now how would you produce a single record for them ?

Run your job using these control cards and see what it produces. Also you are doing all your processing with INREC which get executed even before sort. if your data is unsorted on the first alphabet then you may end up with S0C7 during summation.

for ex: use this data to get S0C7

Code:

A 2008 100   
A 2009 150   
Z 2010 250   
B 2008 050   
C 2008 100   
C 2009 075   
C 2010 250   


You need to define better rules to find the missing value.
Back to top
View user's profile Send private message
vinothsubramanian

New User


Joined: 01 Sep 2006
Posts: 39
Location: Chennai, India

PostPosted: Tue Mar 17, 2009 10:26 pm    Post subject:
Reply with quote

Hi Kolusu,

Thanks for your reply.

As mentioned earlier by me, we will be having only 3 different alphabets for any case. Hence I think there is no need for us to think of putting fourth alphabet in the column1.


However, we can increase the years say from 2001 to 2010 and try. My code works fine for any number of years and with any 3 characters.

Only issue I have now is I couldn't get the header for each column.

Regards,
Ram.
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Convert current to hex value gopurs DFSORT/ICETOOL 4 Wed Feb 15, 2017 5:40 am
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm


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