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     [CLOSED] Convert Columns into a Rows

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Convert Columns into a Rows
 Login/Join
 
Gold member
posted
Hi all,

I have a hold table that contains the following data:

  
COL1  COL2  COL3  COL4
----  ----  ----  ----
1     2     3     4
5     6     7     8


I need to convert the COL columns into a rows. For example the result should be the same as it is shown below:

 
COL
----
1       <- COL1
2       <- COL2
3       <- COL3
4       <- COL4
5       <- COL5
6       <- COL6
7       <- COL7
8       <- COL8
 


Do you have any idea?

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


WebFOCUS 7.6
Windows 2000
Output: HTML, PDF
 
Posts: 72 | Registered: January 14, 2008Report This Post
Master
posted Hide Post
we had the same problem and ended up using a series of sql Union statements to get the final product.

I didn't think to ask on here at the time. Just letting you know that if it isn't possible in webfocus, you can do what you need to with sql.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Expert
posted Hide Post
Simple method as you don't give too many details (and probably haven't done any homework) -
FILEDEF LGMAS DISK LGDATA.MAS
FILEDEF LGDATA DISK LGDATA.FTM
-RUN
-WRITE LGMAS FILE=LGDATA,SUFFIX=COM
-WRITE LGMAS SEGNAME=SEG1
-WRITE LGMAS FIELD=COL1, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL2, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL3, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL4, ,I9 ,I9 , $
-WRITE LGDATA 1,2,3,4,$
-WRITE LGDATA 5,6,7,8,$
-RUN
TABLE FILE LGDATA
PRINT COL1
 OVER COL2
 OVER COL3
 OVER COL4
END

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
Gold member
posted Hide Post
quote:
TABLE FILE LGDATA
PRINT COL1
OVER COL2
OVER COL3
OVER COL4
END


Thanks Tony A but your example just modify the report output. I want to save the result into the hold table as separate column


WebFOCUS 7.6
Windows 2000
Output: HTML, PDF
 
Posts: 72 | Registered: January 14, 2008Report This Post
Expert
posted Hide Post
L_G,

That's what I meant by you not giving too many details. You didn't say that you wanted to hold the data! Try and be more specific in your question.

Try this -
FILEDEF LGMAS DISK LGDATA.MAS
FILEDEF LGDATA DISK LGDATA.FTM
-RUN
-WRITE LGMAS FILE=LGDATA,SUFFIX=COM
-WRITE LGMAS SEGNAME=SEG1
-WRITE LGMAS FIELD=COL1, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL2, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL3, ,I9 ,I9 , $
-WRITE LGMAS FIELD=COL4, ,I9 ,I9 , $
-WRITE LGDATA 1,2,3,4,$
-WRITE LGDATA 5,6,7,8,$
-RUN
SQL
SELECT COL1
  FROM LGDATA
UNION
SELECT COL2
  FROM LGDATA
UNION
SELECT COL3
  FROM LGDATA
UNION
SELECT COL4
  FROM LGDATA
;
HOLD AS Rxxx_H1

Yes, it is SQL against a comma delimited file (but it works), but not knowing what source data yours is it is hard to help you.

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
Guru
posted Hide Post
You can also use the famous McGyver Technique... should be easy to search for McGyver in the forum or even the entire ibi site. Basically, it allows you to output multiple rows with one input row.


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Report This Post
Virtuoso
posted Hide Post
So in your original 'hold creation' are you using across to get the data or just a simple print statement? And additionally, is it really just 8 data items or are there many more that you want to become a column of data in a hold file?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Gold member
posted Hide Post
Hi,

I am not using the across...

And the data is not only 8 data items, it is just an example.

Currently I am using the McGyver technique and it works fine but i am asking because I think that there is another easy method...


WebFOCUS 7.6
Windows 2000
Output: HTML, PDF
 
Posts: 72 | Registered: January 14, 2008Report This Post
Virtuoso
posted Hide Post
L_G

if you show us the master of this table, that might be helpful.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Master
posted Hide Post
You could also do this in Dialog Manager using a -REPEAT, -READ NOCLOSE, -WRITE NOCLOSE.

-SET &EOF = 'N';
-REPEAT RWLOOP UNTIL &EOF = 'Y'
-READ INPUT NOCLOSE &VAR1.A8
-SET &EOF=IF &RETCODE EQ 0 THEN 'N' ELSE 'Y';
-IF &EOF EQ 'Y' THEN GOTO -RWLOOP;
-WRITE OUTPUT NOCLOSE &VAR1
-RWLOOP
-ENDREPEAT
-CLOSE INPUT
-CLOSE OUTPUT


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Silver Member
posted Hide Post
This is my first time to use the Mcgyver technique. In this technique,

JOIN BLANK WITH RPH IN CAR TO BLANK IN FSEQ AS AJ
DEFINE FILE CAR
BLANK/A1 WITH RPH = ' ' ;
VERBOBJ/D7 = IF COUNTER EQ 1 THEN RCOST ELSE
IF COUNTER EQ 2 THEN DCOST ELSE
IF COUNTER EQ 3 THEN SALES ELSE
IF COUNTER EQ 4 THEN MPG ELSE
IF COUNTER EQ 5 THEN RPH ELSE 0;
TITLE_VO/A13 = DECODE COUNTER (1 'RETAIL_COST' 2 'DEALER_COST'
3 'SALES' 4 'MILES_PER_GAL'
5 'REV_PER_HOUR' ELSE ' ');
END
TABLE FILE CAR
SUM VERBOBJ AS ' '
ACROSS SEATS
ACROSS BODYTYPE
BY COUNTRY
BY COUNTER NOPRINT
BY TITLE_VO AS ' '
IF COUNTER LE 5
END

What does the FSEQ contains? I've seen how to create the FSEQ file but what does this file contains to make this technique work?


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Guru
posted Hide Post
Another way of doing this type of report is
  
TABLE FILE WHATEVER
PRINT 
 COL1 AS COLUMNA
ON TABLE HOLD AS TABLE1
END
TABLE FILE WHATEVER
PRINT 
 COL2 AS COLUMNA
ON TABLE HOLD AS TABLE2
END
TABLE FILE WHATEVER
PRINT 
 COL3 AS COLUMNA
ON TABLE HOLD AS TABLE3
END
TABLE FILE WHATEVER
PRINT 
 COL4 AS COLUMNA
ON TABLE HOLD AS TABLE4
END
TABLE FILE HOLD1
PRINT 
  COLUMNA
ON TABLE HOLD AS TABLEALL
MORE
 FILE TABLE2
MORE 
 FILE TABLE3
MORE 
 FILE TABLE4
END
TABLE FILE TABLEALL
PRINT
 COLUMNA
END





WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Virtuoso
posted Hide Post
L_G,

Taking a leaf from Tony's book, and using an alternate master:
  
FILEDEF LGMAS DISK LGDATA.MAS
FILEDEF LGDATA DISK LGDATA.FTM
-RUN
-WRITE LGMAS FILE=LGDATA,SUFFIX=COM
-WRITE LGMAS SEGNAME=SEG1
-WRITE LGMAS FIELD=COL1, ,I9 ,A9 , $
-WRITE LGMAS FIELD=COL2, ,I9 ,A9 , $
-WRITE LGMAS FIELD=COL3, ,I9 ,A9 , $
-WRITE LGMAS FIELD=COL4, ,I9 ,A9 , $
-WRITE LGDATA 1,2,3,4,$
-WRITE LGDATA 5,6,7,8,$
-WRITE LGDATA 9,10,11,12,$
-WRITE LGDATA 13,14,15,16,$
-RUN
DEFINE FILE LGDATA
SP/A1=' ';
END
TABLE FILE LGDATA
PRINT SP COL1 COL2 COL3 COL4
ON TABLE SAVE AS COLS
END
-RUN
FILEDEF MCOLS DISK COLS.MAS
-RUN
-WRITE MCOLS FILE=COLS,SUFFIX=FIX
-WRITE MCOLS SEGNAME=SP
-WRITE MCOLS FIELD=SP, ,A1 ,A1 , $
-WRITE MCOLS SEGNAME=COL, PARENT=SP, OCCURS=VARIABLE
-WRITE MCOLS FIELD=COL, ,I9 ,A9 , $
-RUN
TABLE FILE COLS
PRINT COL
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 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     [CLOSED] Convert Columns into a Rows

Copyright © 1996-2020 Information Builders