Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Append The Current Row to the Previous Row

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Append The Current Row to the Previous Row
 Login/Join
 
Platinum Member
posted
I was hoping if some can help me figure out how to append a row to the previous row or point me in the right direction on how to do this. I have a table with three columns (Product Number, Effective Product Number and Effective Date) and if Product Number is the same as the next Product Number then append that row next to the previous row.

Below is an example.

This is what the table looks like:

PRD_N___EFF_PRD_N____EFF_DT
3_______3____________08/16/2012
3_______121__________11/01/2012
3_______48___________06/01/2013
424_____21___________08/16/2012
424_____2____________12/01/2012
424_____21___________07/19/2013
and so on.......

This is what I want it to look like:

PRD_N___EFF_PRD_N____EFF_DT_______NEXT_EFF_PRD_N______NEXT_EFF_DT
3_______3____________08/16/2012___121_________________11/01/2012
3_______121__________11/01/2012___48__________________06/01/2013
3_______48___________06/01/2013___
424_____21___________08/16/2012___2___________________12/01/2012
424_____2____________12/01/2012___21__________________07/19/2013
424_____21___________07/19/2013___

Thanks,
Mike

This message has been edited. Last edited by: Kerry,


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Expert
posted Hide Post
If you sort in the reverse order, then use the LAST command to pick up the previous value.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
Or maybe something like this:

TABLE FILE CAR
 PRINT
 COMPUTE NEW_COUNTRY/A10 = LAST COUNTRY ;
 COMPUTE NEW_CAR/A16     = LAST CAR ;
 COMPUTE NEW_MODEL/A24   = LAST MODEL ;
 COMPUTE NEXT_CAR/A16    = IF (NEW_COUNTRY EQ COUNTRY) THEN CAR ELSE '';
 COMPUTE NEXT_MODEL/A24  = IF (NEW_COUNTRY EQ COUNTRY) THEN MODEL ELSE '';
 BY COUNTRY NOPRINT
 BY CAR     NOPRINT
 WHERE TOTAL NEW_COUNTRY NE '';
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
or
TABLE FILE IBISAMP/CAR
  SUM COMPUTE itemnumber/I2=IF LAST itemnumber EQ 0 THEN 2 ELSE LAST itemnumber + 1 ; BY CAR
ON TABLE HOLD AS FIRST 
END
TABLE FILE IBISAMP/CAR
SUM COMPUTE itemnumber/I2 =  LAST itemnumber + 1 ;
BY CAR AS CAR2
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SECOND
END
JOIN itemnumber IN FIRST TO itemnumber IN SECOND AS J1
TABLE FILE FIRST
PRINT CAR CAR2 BY itemnumber
END




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Platinum Member
posted Hide Post
Thank you suzannah and Dan Satchell for the suggestion.

suzannah,
I am trying your approach but I am having trouble with when the product number changes then dont append row.

Below is what I am getting when I use your code:
PRD_N___EFF_PRD_N____EFF_DT_______NEXT_EFF_PRD_N______NEXT_EFF_DT
3_______3____________08/16/2012___121_________________11/01/2012
3_______121__________11/01/2012___48__________________06/01/2013
3_______48___________06/01/2013___21__________________08/16/2012
424_____21___________08/16/2012___2___________________12/01/2012
424_____2____________12/01/2012___21__________________07/19/2013
424_____21___________07/19/2013___

I need it to look like below:
PRD_N___EFF_PRD_N____EFF_DT_______NEXT_EFF_PRD_N______NEXT_EFF_DT
3_______3____________08/16/2012___121_________________11/01/2012
3_______121__________11/01/2012___48__________________06/01/2013
3_______48___________06/01/2013___
424_____21___________08/16/2012___2___________________12/01/2012
424_____2____________12/01/2012___21__________________07/19/2013
424_____21___________07/19/2013___


Below is my code:

 
TABLE FILE GWM0099A
SUM
COMPUTE itemnumber/I2=IF LAST itemnumber EQ 0 THEN 2 ELSE LAST itemnumber + 1 ; 
BY PRD_N
BY PARNT_EFF_T NOPRINT
BY PARNT_PRD_N 
BY PARNT_EFF_T 
ON TABLE HOLD AS FIRST 
END

TABLE FILE GWM0099A
SUM COMPUTE itemnumber/I2 =  LAST itemnumber + 1 ;
BY PRD_N AS PRD_N2
BY PARNT_EFF_T NOPRINT
BY PARNT_PRD_N AS PARNT_PRD_N2
BY PARNT_EFF_T AS PARNT_EFF_T2
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SECOND
END

JOIN itemnumber IN FIRST TO itemnumber IN SECOND AS J1

TABLE FILE FIRST
PRINT 
PRD_N 
PARNT_PRD_N
PARNT_EFF_T
PARNT_PRD_N2
PARNT_EFF_T2
BY itemnumber
END


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Expert
posted Hide Post
look at each one of your files individually, before you join them, to see if its gunna work.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
Another possible method (just to confuse things Wink) is to utilise the output from LIST and reprocess to achieve what Waz was suggesting (or at least what I think Waz was suggesting Big Grin)
-* File rigugrid.fex
-* this first bit is just to recreate your sample data
FILEDEF RIGUMAS DISK RIGUDAT.MAS
-RUN
-WRITE RIGUMAS
-WRITE RIGUMAS FILENAME=RIGUDAT, SUFFIX=FOC
-WRITE RIGUMAS SEGNAME=SEG01, SEGTYPE=S1
-WRITE RIGUMAS FIELD=PRD_N,     ALIAS=PRD_N,     USAGE=I08, ACTUAL=I08, $
-WRITE RIGUMAS FIELD=EFF_PRD_N, ALIAS=EFF_PRD_N, USAGE=I08, ACTUAL=I08, $
-WRITE RIGUMAS FIELD=EFF_DT,    ALIAS=EFF_DT,    USAGE=MDYY, ACTUAL=MDYY, $
CREATE FILE RIGUDAT
MODIFY FILE RIGUDAT
FREEFORM PRD_N EFF_PRD_N EFF_DT
DATA
3,3,08/16/2012,$
3,121,11/01/2012,$
3,48,06/01/2013,$
424,21,08/16/2012,$
424,2,12/01/2012,$
424,21,07/19/2013,$
END
-* OK data recreated, let's have some fun.
TABLEF FILE RIGUDAT
 LIST EFF_PRD_N
      EFF_DT
   BY PRD_N
ON TABLE HOLD AS RIGUHOLD
END
-RUN
-* If you look at the output from the following command,
-* you will understand why I reference E02 :)
CMD TYPE RIGUHOLD.MAS
TABLE FILE RIGUHOLD
PRINT EFF_PRD_N
      EFF_DT
      COMPUTE NXT_EFF_PRD_N/I8 MISSING ON = IF PRD_N EQ LAST PRD_N THEN LAST EFF_PRD_N ELSE MISSING;
      COMPUTE NXT_EFF_DT/MDYY  MISSING ON = IF PRD_N EQ LAST PRD_N THEN LAST EFF_DT    ELSE MISSING;
   BY PRD_N
   BY HIGHEST E02 
ON TABLE HOLD AS RIGUHLD1
END
-RUN
TABLE FILE RIGUHLD1
PRINT EFF_PRD_N
      EFF_DT
      NXT_EFF_PRD_N
      NXT_EFF_DT
   BY PRD_N
   BY E02 NOPRINT
END

Having given you that, the method that Susannah has suggested to you is neat and you are almost there - you just need to work on what you are doing in your two extracts.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
there are so many ways to do this...i think we should have a contest... EVERYBODY post a way...
lets see how many we can get...
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Virtuoso
posted Hide Post
Please ignore my first post - it fails to include the final record in the output. Here's something that does seem to work, based on Waz's suggestion, and using Tony's code to build the test data.

FILEDEF RIGUMAS DISK RIGUDAT.MAS
-RUN
-*
-WRITE RIGUMAS
-WRITE RIGUMAS FILENAME=RIGUDAT, SUFFIX=FOC
-WRITE RIGUMAS SEGNAME=SEG01, SEGTYPE=S1
-WRITE RIGUMAS FIELD=PRD_N,     ALIAS=PRD_N,     USAGE=I08, ACTUAL=I08, $
-WRITE RIGUMAS FIELD=EFF_PRD_N, ALIAS=EFF_PRD_N, USAGE=I08, ACTUAL=I08, $
-WRITE RIGUMAS FIELD=EFF_DT,    ALIAS=EFF_DT,    USAGE=MDYY, ACTUAL=MDYY, $
-*
CREATE FILE RIGUDAT
-*
MODIFY FILE RIGUDAT
 FREEFORM PRD_N EFF_PRD_N EFF_DT
 DATA
 3,3,08/16/2012,$
 3,121,11/01/2012,$
 3,48,06/01/2013,$
 424,21,08/16/2012,$
 424,2,12/01/2012,$
 424,21,07/19/2013,$
END
-*
TABLE FILE RIGUDAT
 PRINT EFF_PRD_N
 BY HIGHEST PRD_N
 BY HIGHEST EFF_DT
 ON TABLE HOLD AS REVERSE
END
-*
DEFINE FILE REVERSE
 NEXT_EFF_PRD_N/I8S = IF (LAST EFF_DT EQ '') OR (LAST PRD_N NE PRD_N) THEN 0  ELSE LAST EFF_PRD_N ;
 NEXT_EFF_DT/MDYY   = IF (LAST EFF_DT EQ '') OR (LAST PRD_N NE PRD_N) THEN '' ELSE LAST EFF_DT ;
END
-*
TABLE FILE REVERSE
 PRINT
  PRD_N
  EFF_PRD_N
  EFF_DT
  NEXT_EFF_PRD_N
  NEXT_EFF_DT
 BY PRD_N   NOPRINT
 BY EFF_DT  NOPRINT
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Platinum Member
posted Hide Post
Thank you all for the suggestions!!!

Tony A and Dan Satchell, your codes worked perfectly!!!


Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats
 
Posts: 121 | Location: California | Registered: June 19, 2008Report This Post
Expert
posted Hide Post
quote:
your codes worked perfectly!!!

As does Susannah's if you fill in with her implications -
TABLE FILE RIGUDAT
  SUM COMPUTE itemnumber/I2 = IF LAST itemnumber EQ 0 THEN 2 ELSE LAST itemnumber + 1 ;
   BY PRD_N
   BY EFF_DT
   BY EFF_PRD_N
ON TABLE HOLD AS FIRST 
END
TABLE FILE RIGUDAT
  SUM COMPUTE itemnumber/I2 =  LAST itemnumber + 1 ;
   BY PRD_N      AS PRD_N2
   BY EFF_DT     AS EFF_DT2
   BY EFF_PRD_N  AS EFF_PRD_N2
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS SECOND
END
JOIN itemnumber IN FIRST TO itemnumber IN SECOND AS J1
TABLE FILE FIRST
PRINT EFF_PRD_N
      EFF_DT
      COMPUTE NEXT_EFF_PRD_N/I8 MISSING ON = IF PRD_N EQ PRD_N2 THEN EFF_PRD_N2 ELSE MISSING;
      COMPUTE NEXT_EFF_DT/MDYY  MISSING ON = IF PRD_N EQ PRD_N2 THEN EFF_DT2    ELSE MISSING;
   BY itemnumber NOPRINT
   BY PRD_N
ON TABLE SET BYDISPLAY ON
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
quote:
what Waz was suggesting (or at least what I think Waz was suggesting


Yes Tony, that was what I was suggesting.

Thanks for doing the coding for me.

Big Grin


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Append The Current Row to the Previous Row

Copyright © 1996-2020 Information Builders