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     rows into columns in excel

Read-Only Read-Only Topic
Go
Search
Notify
Tools
rows into columns in excel
 Login/Join
 
Pam Kratt
Platinum Member
posted
My users want an excel spreadsheet where every row in the table is a row on the report. No problem. Today they tell me I need to join to another table that is one to many (up to 10 rows on the table we’re joining to for each record on the primary table), and each of the rows in the joined table needs to be a column on the excel spreadsheet (instead of a row). So, for each account we still only have 1 row, but we may have 10 extra columns if there are 10 rows in the joined table. I can force it to always be 10 columns (in case there are only 3 rows for example on the joined table) if that is easier.

How would I do this? Make a row a column without it getting super complicated? In the table we are joining to, they would like the account # that is found as one field in the data to be the column heading and the data in the field will be the amount for that account #. Both of these fields are in the table we are joining to.

Any suggestions?


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Leah
Virtuoso
posted Hide Post
Have you considered the McGyver technique and a series of defines? Would it suit your needs. Can the columns be collapsed or is the data unique in each.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
What about ACROSS?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Pam Kratt
Platinum Member
posted Hide Post
I've heard of the Mcgyver technique, but I've never seen it or used it. What do you mean by "can the columns be collapsed"? The data in the columns can be unique or is the data unique in each ... are you meaning the table I'm joining to? It is possible that the data may NOT be unique in the table I'm joining to (I'm still trying to get confirmation on this).


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Pam Kratt
Platinum Member
posted Hide Post
Across was what I thought I would use but I get an error (FOC031) THE MAXIMUM NUMBER OF 'ACROSS' VALUES HAS BEEN EXCEEDED. I've never used across before, so I've been reading about it to see what I'm doing wrong.


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
FrankDutch
Virtuoso
posted Hide Post
how about using
Copy and Transpose in EXCEL....




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
Danny-SRL
Virtuoso
posted Hide Post
Pam,
How many fields are there in the second file and what are their data types?


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
Pam Kratt
Platinum Member
posted Hide Post
this is the master for the 2nd file. I join to it using lumpsumid. The combo of lumpsumid and srceacctnbr are unique. I'm grabbing srceacctnbr and srceamt for my excel spreadsheet from this table.

FILE=NBTBLSRC, SUFFIX=DB2 ,$ SEGNAME=NBTBLSRC, SEGTYPE=S0 ,$ FIELD=LUMPSUMID ,LUMPSUMID,
A20 ,A20 ,MISSING=OFF,$
FIELD=SRCEACCTNBR ,SRCEACCTNBR,
A12 ,A12 ,MISSING=OFF,$
FIELD=PLANNBR ,PLANNBR, A12 ,A12 ,MISSING=OFF,$
FIELD=FEDTAXID ,FEDTAXID,
A9 ,A9 ,MISSING=OFF,$
FIELD=PYMTTYPE ,PYMTTYPE,
A2 ,A2 ,MISSING=OFF,$
FIELD=SRCEAMT ,SRCEAMT, P14.2 ,P7 ,MISSING=OFF,$
FIELD=PSTGDTE ,PSTGDTE, P8 ,P4


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
Here's a CAR-based example that may give you an idea or two.

TABLE FILE CAR
SUM COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS H001
END

TABLE FILE CAR
SUM
WHEELBASE
COMPUTE WBNO/I4 = IF COUNTRY EQ LAST COUNTRY THEN WBNO + 1 ELSE 1;
BY COUNTRY
BY WHEELBASE NOPRINT
ON TABLE HOLD AS H002
END

JOIN COUNTRY IN H001 TO ALL COUNTRY IN H002 AS J1

TABLE FILE H001
SUM
WHEELBASE
BY COUNTRY
ACROSS WBNO
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Pam Kratt
Platinum Member
posted Hide Post
thanks Francis. That made sense.


webfocus 8.105M; os: windows; pdf, html, exl2k, csv
 
Posts: 179 | Registered: November 10, 2004Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Pam,
So you want for each record in the first file all the SRCEAMT fields from the second file on the same row.
You can do the following:
TABLE FILE NBTBLSRC
LIST SRCEAMT
BY LUMPSUMID
ON TABLE HOLD AS PAM FORMAT FOCUS INDEX LUMPSUMID
END
JOIN LUMPSUMID IN firstfile TO ALL LUMPSUMID IN PAM AS A_
TABLE FILE firstfile
SUM fieldsfromfirstfile
BY LUMPSUMID
SUM SRCEAMT
ACROSS LIST AS ''
BY LUMPSUMID
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
Danny-SRL
Virtuoso
posted Hide Post
Pam,
Now if you want to have more than 1 field from NBTBLSRC, and have each field on a different line, I have a solution for that too...


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     rows into columns in excel

Copyright © 1996-2020 Information Builders