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] Split Single Column into Multiple Columns

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Split Single Column into Multiple Columns
 Login/Join
 
Guru
posted
Hi,

I have a report with one column of data that has about 300 rows in it. Is there a way to split the data from that one column into 5 columns each with 60 rows? Is there something equivalent to FOLD-LINES for columns?

Thanks,

Dan Pinault

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


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Virtuoso
posted Hide Post
Your best bet is to use a technique I commonly used in producing 3-up labels. You will basically define fields which will serve as X and Y for each row, and then sort by x and across y. I've seen similar code on the forum. My example is from way back from my early FOCUS years, and someone may be able to simplify it, but I also needed to control page-breaks as well as columns and rows.
This produces 3 columns of 53 records each, just like a newspaper column would read, with page-breaks.
DEFINE FILE FILENAME
XROW/I2=IF XROW GT 53 THEN 1 ELSE XROW + 1;
XCOLUMN2/I1=IF XROW EQ 1 THEN XCOLUMN + 1 ELSE XCOLUMN;
XCOLUMN1/I1=XCOLUMN2-((INT(XCOLUMN2/3))*3);
XCOLUMN/I1=IF XCOLUMN1 EQ 1 THEN 1 ELSE
           IF XCOLUMN1 EQ 2 THEN 2 ELSE
           IF XCOLUMN1 EQ 0 THEN 3;
XPAGE/I2=IF (XCOLUMN EQ 1 AND XROW EQ 1) THEN XPAGE + 1 ELSE XPAGE;
END
TABLE FILE FILENAME
PRINT FIELD1 AS ''
BY XPAGE NOPRINT
ACROSS XCOLUMN NOPRINT
BY XROW NOPRINT
ON XPAGE PAGE-BREAK
END


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
Dan

What would be the way to split it in 5 columns?

Suppose you have 300 products, give them two sequence numbers, one goes from 1 to 5 the second from 1 to 60. now you can present them by the second number and across the first.




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
Virtuoso
posted Hide Post
Darin

IMHO your concept has one wrong line.
I would use SUM instead of PRINT.




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
Virtuoso
posted Hide Post
Yes a label like report is the way to go, and if you use PRINT instead of SUM you get a rather strange looking report. Though I will admit the first time I used it I didn't have the experts out here to ask, then I discovered FOCUS-L list serve which is no more.


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

I agree with Darin, define the columns by counter and SUM with ACROSS

Regards

Waz

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


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
Sure 'nuff. I went back to my code to see if I really had a print in there, and I had a WRITE! Old stuff! Anyway, that would be the equivalent of a SUM. I had changed that line to remove my fieldnames and just stuffed a PRINT back in there.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Silver Member
posted Hide Post
I used the code you provided to split the column into two columns. However, I cannot seem to figure out how to retain my css stylings for the second column. How do you reference the second column and rows?

Thanks,

slim007


7.1.4
UNIX
Excel, PDF, HTML
 
Posts: 36 | Registered: April 09, 2008Report This Post
Virtuoso
posted Hide Post
Use the following code. The key is the ACROSSCOLUMN keyword. Change the word SALES in the stylesheet to be the field name of the data you are showing in the columns.

TABLE FILE CAR
SUM SALES
BY COUNTRY
ACROSS CAR
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=SALES, COLOR=RED,$
ENDSTYLE
END


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Silver Member
posted Hide Post
You Rock, Mickey! That seemed to fix the problem.

Thanks!

Stephanie


7.1.4
UNIX
Excel, PDF, HTML
 
Posts: 36 | Registered: April 09, 2008Report This Post
Virtuoso
posted Hide Post
Here is a parameterized solution:

  
-* PP=No. of elements per page
-* CC=No. of cols; LL=No. of lines per col
-SET &LL=10;
-SET &CC=4;
-SET &PP=&LL * &CC;

DEFINE FILE FSEQ
PP/I3=(COUNTER - 1) / &PP;
CC/I2=(COUNTER - &PP * PP - 1) / &LL ;
LL/I2=COUNTER - &PP * PP - CC * &LL  - 1;
SP/A1=' ';
END
-RUN
TABLE FILE FSEQ
SUM COUNTER CHAR SP
BY PP NOPRINT PAGE-BREAK
BY LL NOPRINT 
ACROSS CC NOPRINT
ON TABLE SET NODATA ' '
END


the fseq file is my mcguyver file:
  
 FILENAME=FSEQ,SUFFIX=FIX,
    DATASET=C:\IBI\APPS\XFOCUS\FSEQ.DAT
  SEGNAME=CHAR1,SEGTYPE=S0
   FIELDNAME=BLANK,BLANK,A1,A1,$
  SEGNAME=CHARS,SEGTYPE=S0,PARENT=CHAR1,OCCURS=VARIABLE
   FIELDNAME=CHAR,CHARS,A1,A1,$
   FIELDNAME=COUNTER,ORDER,I2,I4,$

Data:
 FILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVERFILEFORMCGUYVER



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
Gold member
posted Hide Post
Will ACROSS display a lot of empty 'cells' in the columns that are created that were not in the 'contiguous' list of 300 ?

Examples :


TABLE FILE CAR
HEADING
"Without Using ACROSS..."
"Produces 10 rows with 1 cell each = 10 cells"
SUM SALES
BY COUNTRY
BY CAR
END

TABLE FILE CAR
HEADING
"Using ACROSS..."
"Produces 5 rows of 10 cells each = 50 cells"
SUM SALES
BY COUNTRY
ACROSS CAR
ON TABLE SET STYLE *
TYPE=DATA, ACROSSCOLUMN=SALES, COLOR=RED,$
ENDSTYLE
END

I'm trying to accomplish the same thing as Dan Pinault, maybe using column breaks (as in MS Word) ?

Is there a command such as ON ROW-COUNT COLUMN-BREAK ?


WF 7.6.4 & 5.3
Charles Lee
 
Posts: 93 | Registered: June 17, 2008Report This Post
Virtuoso
posted Hide Post
Charlz,

1. Please update your signature

2. If you looked at Darin's example and at mine, you will see that the occurences of our records in the file are numbered. It is this number that allows the creation of columns and rows, NOT the values of fields. If you don't have a record number in your data base you can create it either with a DEFINE or by creating a HOLD file using the LIST verb and then applying the column/row paradigm to the generated record number.

See (and try) this example:
  
-* Charlz.fex
-SET &LL=5;
-SET &CC=4;
-SET &PP=&LL * &CC;
-* &LL=number of lines per page; &CC=number of columns per page
-*
TABLE FILE CAR
LIST SALES
RANKED BY COUNTRY
BY CAR
ON TABLE HOLD
END
-RUN
DEFINE FILE HOLD
PP/I3=(LIST - 1) / &PP;
CC/I2=(LIST - &PP * PP - 1) / &LL ;
LL/I2=LIST - &PP * PP - CC * &LL  - 1;
SP/A1=' ';
END
-RUN
TABLE FILE HOLD
SUM COUNTRY CAR SALES SP AS '.'
BY PP NOPRINT PAGE-BREAK
BY LL NOPRINT
ACROSS CC NOPRINT
ON TABLE SET NODATA ' '
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
Gold member
posted Hide Post
Daniel,

Yes, that's an elegant solution that accomplishes it, without the extra empty NULL cells in the output.

It has a few components with which I was not familiar, so thanks for the intro !

Charles Lee

PS:
I'll check into my 'signature' and update it...
Thanks gain for the heads-up...
CL

Thanks...

This message has been edited. Last edited by: Charlz,
 
Posts: 93 | Registered: June 17, 2008Report This Post
Virtuoso
posted Hide Post
Charles,

You're welcome. Another little insight:
1. In the DEFINE the calculations of the column, line and page are a bit of integer arithmetic.
2. In the first TABLE you will notice that I used both LIST and RANKED. This is because LIST numbers the output lines but when there is a BY clause, the numbering restarts each time the value of the first BY changes. However when RANKED is also used then LIST numbers all lines sequentially. Some of the hidden, but so useful, properties of Focus!


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
Guru
posted Hide Post
Thanks everyone for the insightful comments in this thread.

Daniel -

I have a slight twist to your approach of using the LIST and RANKED BY commands. I'm trying to get an aggregated data set and then break it into columns. When I use the code shown below the LIST numbers are 'out of order'. It appears that the LIST does its numbering before any final sorting.

I know I could get around this by using 2 HOLD files and waiting until the 2nd HOLD file to add the LIST numbering. I'm wondering if there is a way to get the numbers in the right order on the first pass. Any ideas?

Thanks!

Dan

  
TABLE FILE CAR
LIST
SALES
RANKED BY BODYTYPE
BY TOTAL HIGHEST SALES
BY CAR
END


7.7.05M/7.7.03 HF6 on Windows Server 2003 SP2 output to whatever is required.
 
Posts: 393 | Location: St. Paul, MN | Registered: November 06, 2007Report This Post
Virtuoso
posted Hide Post
The implementation of BY TOTAL requires two stages: The first stage assembles an answer set (or "internal matrix") in the normal manner, ignoring the BY TOTAL phrases.

Then a second internal matrix is formed (with room for the additional sort columns), and the rows of the original answer set are used as input transactions to populate the second one, in the process re-sorting them as appropriate.

All Computes take place as part of the first stage. (So all references to LAST are with respect to the first-stage sort order, based on the plain BYs and ignoring the BY TOTALs)

Assignment of the LIST values is implemented as a hidden Compute, and also happens within the first stage. -- Didn't have to be that way, but as it stands you'll have to use an intermediate HOLD: whether you use LIST or compute your own, it has to be done on the HOLD file in order to yield the correct integer series.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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] Split Single Column into Multiple Columns

Copyright © 1996-2020 Information Builders