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     how to do transpose of columns to rows

Read-Only Read-Only Topic
Go
Search
Notify
Tools
how to do transpose of columns to rows
 Login/Join
 
kummy
Member
posted
I have the following columns


media_id dow_id on off
-------- ------ --- -----
780748 1 0 100
780748 1 500 2400
780748 2 0 100
780748 2 500 2400
780748 3 0 100
780748 3 500 2400
780748 4 0 100
780748 4 500 2400
780748 5 0 100
780748 5 500 2400
780748 6 0 100
780748 6 500 2400
780748 7 0 100
780748 7 500 2400

I want the transpose of the column to rows in the following format


1 2 3 4 5 6 7
----------------------------------------------------------------------------------------------------------------------------
media_id on off on off on off on off on off on off on off
-------- ------------ ------------ ------------ ------------ ------------ ------------ ------------

780748 0 100 0 100 0 100 0 100 0 100 0 100 0 100
500 2400 500 2400 500 2400 500 2400 500 2400 500 2400 500 2400

how to achieve this in webFOCUS?
 
Posts: 23 | Registered: April 20, 2007Report This Post
Qalqili
Platinum Member
posted Hide Post
Hi Kummy

try to put the media_id AS ACROSS and the rest of field as Detailed [PRINT]


hope that will help


WF 7.7.0.3HF3 / WinXP- WF-Client & Apache / DevStd 7.7.0.3HF3 win XP.
 
Posts: 118 | Registered: February 08, 2006Report This Post
Alan B
Virtuoso
posted Hide Post
Try:
  
DEFINE FILE Fn
FLOW/I1 = IF DOW_ID EQ LAST DOW_ID THEN 1 ELSE 0;
END
TABLE FILE Fn
SUM ON OFF
ACROSS DOW_ID AS ''
BY MEDIA_ID
BY FLOW NOPRINT
END

Make sure the data is coming in in DOW_ID order.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
kummy
Member
posted Hide Post
thank you all i used oracle analytical functions to fix the issue.
 
Posts: 23 | Registered: April 20, 2007Report This Post
mgrackin
Virtuoso
posted Hide Post
Kummy,

If you want to do this with WebFOCUS, the following code should do it for you:

TABLE FILE YOURDATA
SUM ON OFF
BY MEDIA_ID
ACROSS DOW_ID
END

I'm not sure why Alan B's example included a DEFINE field.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Alan B
Virtuoso
posted Hide Post
Mickey

To give the layout asked for! Your example won't give the 0 100 row.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
mgrackin
Virtuoso
posted Hide Post
Thanks for the clarification Alan. It is hard to see what was requested when the columns do not line up in the post.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Leo L
Gold member
posted Hide Post
I have a similar request, except I'm trying to get each value for a specific id on one row.

e.g.

ID VALUE
-- -----
00 0002
00 0005
00 8213
00 1232
01 2222
01 3333
01 2123

And from this get:

ID NEWFIELD
-- -------------------
00 0002 0005 8213 1232
01 2222 3333 2123

so the newfield can vary in length as I do not know how many VALUE exist per ID.

I had defined:

NEWFIELD = IF ID EQ LAST ID THEN (LAST VALUE | VALUE) ELSE VALUE;

but this doesn't continue to add the values together since it doesn't know that the previous field is now different. This would only save the current Value and the previous Value, but not remember any of the other values.

Anybody tackle this before?


Prod: WebFOCUS 7.6.4 - Self Service - Windows Server2003 - Apache Tomcat 5.5
Dev: WebFOCUS 7.6.4 - Self Service - Windows XP SP2 - Apache Tomcat 5.5
 
Posts: 96 | Location: Winnipeg, Manitoba, Canada | Registered: January 22, 2004Report This Post
Alan B
Virtuoso
posted Hide Post
Try:
NEWFIELD/A300V = IF ID EQ LAST ID THEN LAST NEWFIELD | ' ' | VALUE ELSE VALUE;

The V option allows the field to be concatenated to another field, as long as you are on a latter release.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Leo L
Gold member
posted Hide Post
Thanks for the response,

I figured it out about 5 minutes after posting the question... is it just me or you have to put yourself out there before coming out with a solution yourself.

I ended up with:
NEWFIELD/A200 = IF ID NE LAST ID THEN VALUE ELSE LJUST(200, LAST NEWFIELD, 'A190') || (' ' | VALUE);

The issue I had was with the length of field (which both solutions solve) and the other was with the weak concatenation. Once I put the strong concatenation it worked like a charm!


Prod: WebFOCUS 7.6.4 - Self Service - Windows Server2003 - Apache Tomcat 5.5
Dev: WebFOCUS 7.6.4 - Self Service - Windows XP SP2 - Apache Tomcat 5.5
 
Posts: 96 | Location: Winnipeg, Manitoba, Canada | Registered: January 22, 2004Report This Post
Alan B
Virtuoso
posted Hide Post
Of course, Leo, you are right, a strong concat will be needed.

Sometimes you just need a head-on-a-stick to get a solution.

Try the V format for alpha fields sometime, it is a v. nice option.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report 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     how to do transpose of columns to rows

Copyright © 1996-2020 Information Builders