3 Replies Latest reply: Aug 27, 2012 7:41 AM by Sander Janssen RSS

    need help to display result based on specific category

      hi guys,

       

      here is the question,

      I have the following data and need the result as per the image attached

       

      the script is as follows

       

      ***************************************************************************************************************

      /*base data comes from source*/

      BASE_DATA:

      LOAD*INLINE [

      BP     ,     CA     ,     Account_Det_ID

      1234     ,     5555     ,     DU

      1234     ,     5555     ,    DM

      1234     ,     6666     ,     DM

      5678     ,     1111     ,     DU

      5678     ,     2222     ,     DU

      ];

       

      WITH_ROWNUM:

      NOCONCATENATE LOAD

      *,

      1 as RowNum

      resident BASE_DATA;

       

      drop table BASE_DATA;

       

      FINAL_TABLE:

      NOCONCATENATE LOAD

      BP,

      CA,

      Account_Det_ID,

      if(BP=peek("BP",-1),peek("RowNum",-1)+1,1)as RowNum

      resident WITH_RONUM order by BP,CA,Account_Det_ID;

      drop table ITH_ROWNUM;

       

      ************************************************************************************************************************

       

      Result that I'm in need of is

      based on the every 1st RowNum of a BP the result should be displayed as below

      Result.jpg

      now, what is that I need to modify or use in the script in order to display the result as above

       

      kindly help me out friends

       

      Regards

      Anand