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] Converting Row Values to Column Names

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Converting Row Values to Column Names
 Login/Join
 
Member
posted
I have a input file look like this...

ProdKey time_int Total_Unit sold_unit
A-B YTD 200 175
A-B Q3 50 45
A-B Q4 80 70
A-B 01-Nov 5 3
A-B 15-Nov 4 3

I need output report like this

GOOD UNITS

ProdKey TOTAL_YTD sold_unit_YTD Q3 Q4 01-Nov 15-Nov
A-B 200 175 90% 87.5% 60% 75%

YTD will be the sum of units but rest will be percentages sold_unit/total_unit * 100
I tried ACROSS it doesn't allow me to calculate the percentages for rest columns.

Any suggestions?

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


WEBFOCUS 8105m
Windows, All Outputs
 
Posts: 8 | Registered: November 28, 2012Report This Post
Master
posted Hide Post
CRICKETMIND,

I would probably do something like this:

 
SET ASNAMES = ON
SET PRINTLIST = HOLDONLY

DEFINE FILE INPUT_FILE
TOTAL_YTD/D12.2=IF TIME_INT EQ 'YTD' THEN TOTAL_UNIT ELSE 0;
TOTAL_SOLD/D12.2=IF TIME_INT EQ 'YTD' THEN TOTAL_SOLD ELSE 0;
TABLE FILE INPUT_FILE
SUM TOTAL_YTD AS BY_TOTAL_YTD
TOTAL_SOLD AS BY_TOTAL_SOLD
BY PRODKEY
-* MULTI-VERB WILL APPLY BY_TOTAL_YTD AND BY_TOTAL_SOLD VALUES TO PRODKEY FOR ALL TIME_INT'S THIS IS IMPORTANT FOR OUR SECOND HOLD
SUM 
TOTAL_UNIT 
SOLD_UNT 
COMPUTE PERCNT/D12.2=(SOLD_UNIT/TOTAL_UNIT) * 100;
BY PRODKEY
BY TIME_INT
ON TABLE HOLD AS MY_HOLD
END

TABLE FILE MY_HOLD
SUM PERCNT
ACROSS TIME_INT 
-* BY GOING ACROSS THE TIME_INT AND HOLDING IT, THE HOLD PROCESS WILL CREATE A FIELD FOR EACH ITERATION OF TIME_INT.  
-* THIS FIELD WILL NOW HOLD THE PERCENTAGE AS ITS VALUE
BY PROD_KEY
-* BECAUSE OF THE MULTI-VERB ABOVE, YOU CAN USE A BY STATEMENT ON THE TOTAL FIELDS INSTEAD OF SUMMING THEM SINCE EACH UNIQUE VALUE OF PRODKEY ONLY HAS
-* ONE VALUE FOR EACH TOTAL FIELD.
BY BY_TOTAL_YTD AS 'TOTAL_YTD'
BY BY_TOTAL_SOLD AS 'TOTAL_SOLD'
ON TABLE HOLD AS OUTPUT_VALUES
END

-* PRINT THE RESULTS OUT ONTO THE SCREEN.  AGGREGATION AND SORTING HAS BEEN HANDLED IN THE PREVIOUS HOLD FILE
TABLE FILE OUTPUT_VALUES
PRINT *
END


There are other ways to do this. I have a fex where I use this concept to turn columns into fields and then I use a -READ to put the field names into a flat file, then use a loop to pull them out and place them where I want in the final output. This is probably more complex then what you want.

You could also do the across/hold concept above and join back to the another hold file if you don't want to use a multi-verb.

I hope some of this helps.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Expert
posted Hide Post
Use "buckets" within define statements, something like-
  SOLD_YTD/D20  = IF TIME_INT EQ 'YTD'    THEN SOLD_UNIT  ELSE 0;
  Q3/D6.1%      = IF TIME_INT EQ 'Q3'     THEN SOLD_UNIT / TOTAL_UNIT * 100  ELSE 0;

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
Member
posted Hide Post
eric.woerle - You are a genius. I am still a novice in webfocus and had no concept of multi-verb until now. Thanks a bunch.


WEBFOCUS 8105m
Windows, All Outputs
 
Posts: 8 | Registered: November 28, 2012Report This Post
Master
posted Hide Post
CRICKETMIND,

Multi-verbs took me a while to get my head around when I first started out, but they can be very powerful. I don't use them very often, but sometimes it makes things so much easier in that I don't have to create multiple hold files and join things back together.

Tony,

I was thinking along your lines originally too, but then I went with the assumption that you don't always know the time intervals the need to be defined. Maybe its dynamic, or the user can change the time frames and give it different names. Since I assumed that the actual time intervals was unknown, I had to hold the fields as an across. But If you always know the time intervals, then defining it is the simpler and better solution. One pass to report instead of my 3 passes.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Member
posted Hide Post
You are absolutely right. My dates/time intervals are dynamic.It's rolling fork waters and rolling last four week ending dates. Another point worth mentioning - ASNAMES = ON . Thanks again.


WEBFOCUS 8105m
Windows, All Outputs
 
Posts: 8 | Registered: November 28, 2012Report This Post
Virtuoso
posted Hide Post
Multi-Verb should not be required here. If everything is as described then you could use:
DEFINE FILE filename
cntr/I4        = IF ProdKey EQ LAST ProdKey THEN cntr+1 ELSE 1;
TotalYTD/I6    = IF TimeFrame EQ 'YTD' THEN TotalUnit ELSE TotalYTD; 
UnitSoldYTD/I6 = IF TimeFrame EQ 'YTD' THEN SoldUnit  ELSE UnitSoldYTD; 
D_TimeFrame/A6 MISSING ON = IF TimeFrame NE 'YTD' THEN TimeFrame ELSE MISSING;
Percent/F6.2% MISSING ON  = IF D_TimeFrame NE MISSING THEN (SoldUnit/TotalUnit)*100 ELSE MISSING; 
END
TABLE FILE filename
SUM      Percent
ACROSS   cntr NOPRINT
ACROSS   D_TimeFrame AS ''
BY       ProdKey
BY       TotalYTD
BY       UnitSoldYTD
ON TABLE SET HIDENULLACRS ON
ON TABLE SET PAGE NOLEAD
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
quote:
Originally posted by Alan B:
Multi-Verb should not be required here. If everything is as described then you could use:
DEFINE FILE filename
cntr/I4        = IF ProdKey EQ LAST ProdKey THEN cntr+1 ELSE 1;
TotalYTD/I6    = IF TimeFrame EQ 'YTD' THEN TotalUnit ELSE TotalYTD; 
UnitSoldYTD/I6 = IF TimeFrame EQ 'YTD' THEN SoldUnit  ELSE UnitSoldYTD; 
D_TimeFrame/A6 MISSING ON = IF TimeFrame NE 'YTD' THEN TimeFrame ELSE MISSING;
Percent/F6.2% MISSING ON  = IF D_TimeFrame NE MISSING THEN (SoldUnit/TotalUnit)*100 ELSE MISSING; 
END
TABLE FILE filename
SUM      Percent
ACROSS   cntr NOPRINT
ACROSS   D_TimeFrame AS ''
BY       ProdKey
BY       TotalYTD
BY       UnitSoldYTD
ON TABLE SET HIDENULLACRS ON
ON TABLE SET PAGE NOLEAD
END


I think Percent calculation should be done using compute otherwise it will change the result. Because first it will calculate the result and aggregate it and gives the percent value more than 100 which is not correct.


WebFOCUS 7703
Windows, All Outputs
pravinsinghwebfocus.blogspot.com
 
Posts: 51 | Location: WebFOCUS 7.7.03 | Registered: June 19, 2014Report This Post
Virtuoso
posted Hide Post
quote:
I think Percent calculation should be done using compute otherwise it will change the result. Because first it will calculate the result and aggregate it and gives the percent value more than 100 which is not correct.


You are correct Pravin, under general circumstances. However, this is why I said "if everything is as described". The example shows only one value line per ProdKey/TimeFrame combination, so DEFINE would be ok here.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Master
posted Hide Post
Good point Alan!

I didn't think about using last like that. Although, I would add a pass before your request to order and aggregate everything. This again falls outside of your "If everything is as described" statement. But even if its not as described, adding a first pass would guarentee your data is in the necessary order to properly use the last command, and it would also resolve Pravin's need for a compute. Although if Pravin really wants those computes, he could still use them Smiler


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report 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] Converting Row Values to Column Names

Copyright © 1996-2020 Information Builders