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]How to convert the multiple fields from individual column to a single column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]How to convert the multiple fields from individual column to a single column
 Login/Join
 
Guru
posted
Hi,

My output which Iam getting in the report is like this :
 PAGE 1
Region		Data			CNT_IF_3_65	CNT_IF_2_65	CNT_IF_1_65	CNT_IF_0_65	CNT_IF_3_80	CNT_IF_2_80	CNT_IF_1_80	CNT_IF_0_80	CNT_IF_3_90	CNT_IF_2_90	CNT_IF_1_90	CNT_IF_0_90	CNT_IF_YTD_90
E	    % Congested w/o PE Total	  3.04%		3.06%		2.99%		2.86%		1.07%		1.13%		1.02%		1.00%		0.52%		0.49%		0.46%		0.46%		0.49%
MW	    % Congested w/o PE Total	  4.90%		4.86%		4.48%		4.53%		2.27%		2.26%		1.96%		2.08%		1.37%		1.26%		1.12%		1.16%		1.28%
MTW	    % Congested w/o PE Total	  2.60%		2.72%		2.52%		2.27%		1.55%		1.63%		1.48%		1.32%		0.95%		0.99%		0.87%		0.74%		0.92%
W	    % Congested w/o PE Total	  4.10%		4.33%		4.11%		4.06%		2.30%		2.41%		2.11%		2.17%		1.32%		1.38%		1.20%		1.22%		1.30%
TOTAL CTL   % Congested w/o PE Grand Total3.64%         3.73%		3.51%		3.39%		1.81%		1.88%		1.66%		1.65%		1.05%		1.05%		0.92%		0.91%		1.01% 


But I need my output to be like :
Region		Data		New_field
value1 		value2		CNT_IF_3_65	
				CNT_IF_2_65	
				CNT_IF_1_65	
				CNT_IF_0_65	
				CNT_IF_3_80	
				CNT_IF_2_80	
				CNT_IF_1_80	
				CNT_IF_0_80	
				CNT_IF_3_90	
				CNT_IF_2_90	
				CNT_IF_1_90	
				CNT_IF_0_90	
				CNT_IF_YTD_90  


I have tried doing it with OVER, Across etc and nothing is giving me the expected result.

Could anyone please help?
Thanks a lot in advance for all your help!

Regards,
IP

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


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Virtuoso
posted Hide Post
This could be one way
-SET &LOOP  = 0;
-SET &NBFLD = 3;
FILEDEF PIVOTED DISK BASEAPP/PIVOTED.ftm

-REPEAT PIVOTCOL FOR &LOOP FROM 1 TO &NBFLD STEP 1

DEFINE FILE CAR
IND      /I3   = &LOOP.EVAL;
PIVOTFLD /A40V = IF IND EQ 1 THEN COUNTRY ELSE IF IND EQ 2 THEN CAR ELSE MODEL;
END
TABLE FILE CAR
PRINT PIVOTFLD
      COUNTRY NOPRINT
BY IND
ON TABLE HOLD AS PIVOTED FORMAT ALPHA
END
-RUN

FILEDEF PIVOTED DISK BASEAPP/PIVOTED.ftm (APPEND
-PIVOTCOL

TABLE FILE PIVOTED
PRINT *
END
-RUN


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Also possible use PUTDDREC -

FILEDEF PUTDD1 DISK PUTDD1.TXT
-RUN

TABLE FILE CAR
PRINT COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, COUNTRY, 50, OUT1);
      COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, CAR, 50, OUT1);
      COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, MODEL, 50, OUT1);
ON TABLE SAVE -* No name - no want output anyway!
END
-RUN

CMD TYPE PUTDD1.TXT


Create own synonym and is ready Smiler

Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 106 | Registered: April 06, 2009Report This Post
Expert
posted Hide Post
Kofi, that's very interesting! I've never used nor knew about the PUTDDREC function. I can see its usefulness. Thanks for the education.


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
Platinum Member
posted Hide Post
I thank you Francis, and all other Expert who give great information and help me learn by reading all post on forum.

I also RTFM Big Grin

Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 106 | Registered: April 06, 2009Report This Post
Virtuoso
posted Hide Post
Pal,
This seems to me an example for the use of an alternate master.
If you HOLD your output you will create a file with the following fields:
  
Region		Data			CNT_IF_3_65	CNT_IF_2_65	CNT_IF_1_65	CNT_IF_0_65	CNT_IF_3_80	CNT_IF_2_80	CNT_IF_1_80	CNT_IF_0_80	CNT_IF_3_90	CNT_IF_2_90	CNT_IF_1_90	CNT_IF_0_90	CNT_IF_YTD_90


Create a new MASTER with 2 segments.
SEGMENT1 has fields
Region
Data

SEGMENT2 has PARENT=SEGMENT1, OCCURS=VARIABLE
and field
NEW_FIELD

Then you just have to
PRINT NEW_FIELD
BY REGION
BY DATA


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
Expert
posted Hide Post
quote:
I've never used nor knew about the PUTDDREC function


One of the nice things about PUTDDREC is that you can supply the length of the record either as a literal or a function, so you have the possibility of removing trailing spaces from the file.

quote:
OCCURS=VARIABLE


This was my first though, depending on the data source.


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
Guru
posted Hide Post
Hi,

Martin - Iam using your technique/code.
Others - Thanks for the suggestions and Iam glad that Iam learning something new everyday Smiler

This is how my code is now :
 -SET &ECHO='ALL';
-INCLUDE IBFS:/WFC/Repository/Myfolder/Ref_folder/oracle_conn.fex
-RUN

-SET &LOOP  = 0;
-SET &NBFLD = 13;
FILEDEF PIVOTED DISK NET_CAP_RPT/PIVOTED.ftm

-REPEAT PIVOTCOL FOR &LOOP FROM 1 TO &NBFLD STEP 1

SQL SQLORA PREPARE REPORT1 FOR
SELECT * FROM CONN1.TABLE_REF;
END

TABLE FILE REPORT1
PRINT
*
BY DIVISION
BY REGION 
BY STATE 
WHERE DIVISION NE '' OR '??'
WHERE DATA EQ '% Congested w/o PE' OR '% Congested w/o PE Grand Total'
ON TABLE HOLD AS HOLD_MAIN
END
-RUN

TABLE FILE REPORT1
PRINT 
REPORT_TAB 
DIVISION 
REGION 
STATE 
DATA 
CNT_IF_3_65 
CNT_IF_2_65 
CNT_IF_1_65 
CNT_IF_0_65 
CNT_IF_3_80 
CNT_IF_2_80 
CNT_IF_1_80 
CNT_IF_0_80 
CNT_IF_3_90 
CNT_IF_2_90 
CNT_IF_1_90 
CNT_IF_0_90 
CNT_IF_YTD_90
WHERE REPORT_TAB EQ ''
ON TABLE HOLD AS HOLD1 
END
-RUN

DEFINE FILE HOLD1
IND      /I3   = &LOOP.EVAL;
PIVOTFLD /A40V = IF IND EQ 1 THEN CNT_IF_3_65 ELSE IF IND EQ 2 THEN CNT_IF_2_65 ELSE IF IND EQ 3 THEN CNT_IF_1_65 ELSE IF IND EQ 4 THEN CNT_IF_0_65 ELSE IF IND EQ 5 THEN CNT_IF_3_80 ELSE IF IND EQ 6 THEN CNT_IF_2_80 ELSE IF IND EQ 7 THEN CNT_IF_1_80 ELSE IF IND EQ 8 THEN CNT_IF_0_80 ELSE IF IND EQ 9 THEN CNT_IF_3_90 ELSE IF IND EQ 10 THEN CNT_IF_2_90 ELSE IF IND EQ 11 THEN CNT_IF_1_90 ELSE IF IND EQ 12 THEN CNT_IF_0_90 ELSE CNT_IF_YTD_90;
END

TABLE FILE HOLD1
PRINT PIVOTFLD
      DIVISION NOPRINT
	  REGION NOPRINT
	  STATE NOPRINT
      DATA NOPRINT
BY IND
ON TABLE HOLD AS PIVOTED FORMAT ALPHA
END
-RUN

FILEDEF PIVOTED DISK NET_CAP_RPT/PIVOTED.ftm (APPEND
-PIVOTCOL

TABLE FILE PIVOTED
PRINT IND
PIVOTFLD
DATA
BY DIVISION
BY REGION 
BY STATE 
ON TABLE HOLD AS HOLD_PIVOT 
END
-RUN

MATCH FILE HOLD_MAIN
PRINT DATA CNT_IF_3_65 
CNT_IF_2_65 
CNT_IF_1_65 
CNT_IF_0_65 
CNT_IF_3_80 
CNT_IF_2_80 
CNT_IF_1_80 
CNT_IF_0_80 
CNT_IF_3_90 
CNT_IF_2_90 
CNT_IF_1_90 
CNT_IF_0_90 
CNT_IF_YTD_90
BY DIVISION BY REGION BY STATE
RUN
FILE HOLD_PIVOT
PRINT IND PIVOTFLD
BY DIVISION BY REGION BY STATE 
AFTER MATCH HOLD AS HOLD_OUTPUT OLD-OR-NEW
END

TABLE FILE HOLD_OUTPUT
PRINT *
END
-RUN
-EXIT

Output of Hold_output which Iam getting is :
DIVISION	REGION	STATE	DATA	CNT_IF_3_65	CNT_IF_2_65	CNT_IF_1_65	CNT_IF_0_65	CNT_IF_3_80	CNT_IF_2_80	CNT_IF_1_80	CNT_IF_0_80	CNT_IF_3_90	CNT_IF_2_90	CNT_IF_1_90	CNT_IF_0_90	CNT_IF_YTD_90	IND	PIVOTFLD
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	1	27-JAN-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	2	10-FEB-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	3	24-FEB-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	4	10-MAR-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	5	27-JAN-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	6	10-FEB-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	7	24-FEB-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	8	10-MAR-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	9	27-JAN-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	10	10-FEB-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	11	24-FEB-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	12	10-MAR-17
.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	13	YTD AVG 2017
EAST	E	AL	% Congested w/o PE	5.16%	5.67%	5.62%	5.28%	1.93%	2.33%	2.04%	2.10%	1.15%	1.09%	0.95%	1.08%	1.09%	0	
EAST	E	AR	% Congested w/o PE	7.05%	7.32%	8.17%	7.83%	2.84%	3.27%	3.49%	3.28%	1.55%	1.46%	1.79%	1.85%	1.68%	0	
EAST	E	FL	% Congested w/o PE	0.74%	0.93%	0.88%	0.92%	0.20%	0.20%	0.23%	0.18%	0.06%	0.09%	0.08%	0.08%	0.08%	0	
EAST	E	GA	% Congested w/o PE	1.75%	2.61%	2.65%	2.64%	0.88%	0.87%	0.88%	0.88%	0.88%	0.43%	0.44%	0.88%	0.70%	0	
EAST	E	LA	% Congested w/o PE	10.34%	10.04%	10.42%	10.72%	6.41%	7.04%	6.51%	6.33%	3.80%	3.52%	3.78%	3.62%	3.71%	0	
EAST	E	MA	% Congested w/o PE	0.00%	0.00%	100.00%	100.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0	
EAST	E	MD	% Congested w/o PE	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0	
EAST	E	MS	% Congested w/o PE	15.09%	14.42%	14.95%	15.38%	5.66%	6.73%	3.74%	3.85%	0.00%	0.00%	0.00%	0.00%	0.00%	0	
EAST	E	NC	% Congested w/o PE	2.27%	2.00%	1.82%	1.61%	0.33%	0.29%	0.27%	0.33%	0.06%	0.06%	0.06%	0.06%	0.06%	0	
EAST	E	NJ	% Congested w/o PE	1.26%	1.89%	0.42%	0.42%	0.42%	1.26%	0.00%	0.00%	0.21%	1.05%	0.00%	0.00%	0.34%	0	
EAST	E	OK	% Congested w/o PE	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0	
EAST	E	PA	% Congested w/o PE	1.39%	1.63%	0.81%	0.68%	0.35%	0.34%	0.07%	0.07%	0.35%	0.20%	0.07%	0.07%	0.18%	0	
EAST	E	SC	% Congested w/o PE	3.28%	3.30%	3.33%	2.84%	0.23%	0.47%	0.71%	0.47%	0.00%	0.00%	0.00%	0.00%	0.00%	0	
EAST	E	TN	% Congested w/o PE	2.28%	2.76%	2.91%	2.27%	0.33%	0.32%	0.32%	0.49%	0.16%	0.16%	0.16%	0.00%	0.13%	0	
EAST	E	TX	% Congested w/o PE	7.36%	6.87%	6.60%	6.32%	3.19%	2.97%	2.49%	2.41%	1.41%	1.11%	1.00%	1.11%	1.23%	0	
EAST	E	VA	% Congested w/o PE	4.07%	3.74%	4.10%	3.85%	1.31%	1.18%	1.24%	1.22%	0.66%	0.59%	0.52%	0.32%	0.55%	0	
EAST	MW	IA	% Congested w/o PE	7.62%	7.43%	6.94%	6.66%	4.17%	4.09%	3.46%	3.57%	2.77%	2.30%	1.92%	1.87%	2.32%	0	
EAST	MW	IL	% Congested w/o PE	0.42%	0.42%	0.00%	0.00%	0.42%	0.42%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.08%	0	
EAST	MW	IN	% Congested w/o PE	7.42%	6.77%	6.00%	6.10%	2.85%	2.64%	1.58%	1.88%	1.14%	1.15%	0.90%	1.11%	1.11%	0	
EAST	MW	KS	% Congested w/o PE	13.20%	13.97%	17.35%	17.96%	3.40%	2.79%	5.26%	5.19%	2.20%	1.60%	3.12%	3.52%	2.39%	0	
EAST	MW	MI	% Congested w/o PE	1.85%	2.81%	1.75%	2.41%	0.34%	0.53%	0.35%	0.69%	0.34%	0.35%	0.18%	0.17%	0.31%	0	
EAST	MW	MN	% Congested w/o PE	2.63%	2.70%	2.36%	2.24%	1.40%	1.43%	1.19%	1.27%	0.84%	0.84%	0.70%	0.73%	0.81%	0	
EAST	MW	MO	% Congested w/o PE	7.88%	7.53%	7.71%	8.06%	3.04%	2.87%	2.91%	3.27%	1.55%	1.35%	1.35%	1.49%	1.45%	0	
EAST	MW	ND	% Congested w/o PE	3.10%	3.83%	2.89%	2.40%	1.82%	1.64%	1.62%	1.20%	1.09%	1.09%	1.08%	1.05%	1.15%	0	
EAST	MW	NE	% Congested w/o PE	6.48%	6.47%	5.84%	4.93%	3.98%	4.14%	3.40%	2.97%	2.44%	2.38%	2.21%	1.87%	2.31%	0	
EAST	MW	OH	% Congested w/o PE	4.14%	3.90%	3.23%	3.44%	0.84%	0.90%	0.54%	0.77%	0.48%	0.60%	0.36%	0.47%	0.55%	0	
EAST	MW	SD	% Congested w/o PE	6.58%	6.43%	5.96%	4.86%	3.89%	3.89%	3.87%	3.12%	2.99%	3.14%	3.13%	2.43%	2.91%	0	
EAST	MW	WI	% Congested w/o PE	3.89%	3.71%	2.85%	3.52%	1.50%	1.60%	1.04%	1.30%	0.81%	0.70%	0.42%	0.61%	0.76%	0	
TOTAL CTL	TOTAL CTL	TOTAL CTL	% Congested w/o PE Grand Total	3.64%	3.73%	3.51%	3.23%	1.81%	1.88%	1.66%	1.57%	1.05%	1.05%	0.92%	0.85%	1.00%	0	
WEST	MTW	AZ	% Congested w/o PE	1.60%	2.03%	1.91%	1.40%	1.07%	1.23%	1.16%	0.83%	0.68%	0.72%	0.69%	0.51%	0.66%	0	
WEST	MTW	CO	% Congested w/o PE	2.80%	2.79%	2.58%	2.44%	1.63%	1.72%	1.53%	1.42%	0.99%	1.06%	0.88%	0.76%	0.96%	0	
WEST	MTW	NM	% Congested w/o PE	4.49%	4.32%	3.97%	3.41%	2.50%	2.34%	2.10%	1.73%	1.55%	1.41%	1.28%	0.96%	1.35%	0	
WEST	W	ID	% Congested w/o PE	5.10%	5.10%	4.45%	4.30%	2.98%	3.10%	2.29%	2.25%	1.63%	1.63%	1.35%	1.33%	1.52%	0	
WEST	W	MT	% Congested w/o PE	6.56%	6.57%	6.36%	5.31%	4.40%	4.12%	3.86%	3.37%	2.88%	2.89%	2.93%	2.57%	2.79%	0	
WEST	W	NV	% Congested w/o PE	0.99%	0.83%	0.77%	0.70%	0.72%	0.56%	0.42%	0.51%	0.12%	0.06%	0.00%	0.00%	0.05%	0	
WEST	W	OR	% Congested w/o PE	5.08%	5.48%	5.44%	3.45%	2.73%	3.05%	2.63%	1.73%	1.68%	1.64%	1.48%	0.93%	1.41%	0	
WEST	W	UT	% Congested w/o PE	3.26%	3.05%	2.90%	2.72%	1.92%	1.78%	1.48%	1.47%	1.07%	0.98%	0.79%	0.73%	0.95%	0	
WEST	W	WA	% Congested w/o PE	4.14%	4.90%	4.63%	4.33%	2.04%	2.43%	2.34%	2.20%	1.27%	1.58%	1.36%	1.31%	1.39%	0	
WEST	W	WY	% Congested w/o PE	8.77%	8.77%	7.89%	7.10%	5.15%	4.93%	4.27%	3.97%	2.41%	2.74%	2.08%	1.85%	2.32%	0	

The output which I need is :(I have taken a single state from the above output)
DIVISION	REGION	STATE	DATA	           CNT_IF_3_65	CNT_IF_2_65	CNT_IF_1_65	CNT_IF_0_65	CNT_IF_3_80	CNT_IF_2_80	CNT_IF_1_80	CNT_IF_0_80	CNT_IF_3_90	CNT_IF_2_90	CNT_IF_1_90	CNT_IF_0_90	CNT_IF_YTD_90
EAST	    E	    AL	    % Congested w/o PE	5.16%	    5.67%	    5.62%	     5.28%	    1.93%	    2.33%	    2.04%	     2.10%	     1.15%	    1.09%	     0.95%	     1.08%	     1.09%
 
Output should like as shown below(This is the final output needed):
DIVISION REGION STATE PERIOD       MeasType  Meas
EAST     E      AL    27-JAN-17      1       5.16
EAST     E      AL    27-JAN-17      2       5.67
EAST     E      AL    27-JAN-17      3       5.62
EAST     E      AL    10-FEB-17      1       5.28
EAST     E      AL    10-FEB-17      2       1.93
EAST     E      AL    10-FEB-17      3       2.33
EAST     E      AL    24-FEB-17      1       2.04
EAST     E      AL    24-FEB-17      2       2.10
EAST     E      AL    24-FEB-17      3       1.15
EAST     E      AL    10-MAR-17      1       1.09
EAST     E      AL    10-MAR-17      2       0.95
EAST     E      AL    10-MAR-17      3       1.08
EAST     E      AL    YTD AVG 2017   1       1.09
... 


Could you or anyone please let me know how to achieve this? Iam stuck here and not able to proceed further.
Thanks a lot for all your help!

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Virtuoso
posted Hide Post
Hi IP,
You loop for the entire thing when you should only loop once your data has been extracted from SQL.

As I mentioned, you should:
1- Extract your data from SQL and HOLD it as you already did
2- From the HOLD file, extract the date record and save the dates in variables as you did in your second post in the other thread
3- Build the loop for the other records to pivot the 13 columns from the HOLD file.
4- create your report based on new hold file PIVOTED

You also need to keep your key fields if you want your data correctly. Something such as
-INCLUDE IBFS:/WFC/Repository/Myfolder/Ref_folder/oracle_conn.fex
-RUN

SQL SQLORA PREPARE REPORT1 FOR
SELECT * FROM CONN1.TABLE_REF;
END

TABLE FILE REPORT1
PRINT CNT_IF_3_65
      CNT_IF_2_65
      CNT_IF_1_65
      CNT_IF_0_65
      CNT_IF_3_80
      CNT_IF_2_80
      CNT_IF_1_80
      CNT_IF_0_80
      CNT_IF_3_90
      CNT_IF_2_90
      CNT_IF_1_90
      CNT_IF_0_90
      CNT_IF_YTD_90
WHERE REPORT_TAB EQ ''
ON TABLE HOLD AS MYFILE
END
-RUN

-READFILE MYFILE
-RUN
-TYPE &CNT_IF_3_65
-TYPE &CNT_IF_2_65
-TYPE &CNT_IF_1_65
-TYPE &CNT_IF_0_65
-TYPE &CNT_IF_3_80
-TYPE &CNT_IF_2_80
-TYPE &CNT_IF_1_80
-TYPE &CNT_IF_0_80
-TYPE &CNT_IF_3_90
-TYPE &CNT_IF_2_90
-TYPE &CNT_IF_1_90
-TYPE &CNT_IF_0_90
-TYPE &CNT_IF_YTD_90

-SET &LOOP  = 0;
-SET &NBFLD = 13;

FILEDEF PIVOTED DISK NET_CAP_RPT/PIVOTED.ftm

-REPEAT PIVOTCOL FOR &LOOP FROM 1 TO &NBFLD STEP 1

DEFINE FILE REPORT1
IND      /I3   = &LOOP.EVAL;
MEASTYPE /I3   = IF IND LE 4  THEN 1
            ELSE IF IND LE 8  THEN 2
            ELSE IF IND LE 12 THEN 3
            ELSE 13;
PIVOTFLD /A40V = IF IND EQ 1  THEN CNT_IF_3_65
            ELSE IF IND EQ 2  THEN CNT_IF_2_65
            ELSE IF IND EQ 3  THEN CNT_IF_1_65
            ELSE IF IND EQ 4  THEN CNT_IF_0_65
            ELSE IF IND EQ 5  THEN CNT_IF_3_80
            ELSE IF IND EQ 6  THEN CNT_IF_2_80
            ELSE IF IND EQ 7  THEN CNT_IF_1_80
            ELSE IF IND EQ 8  THEN CNT_IF_0_80
            ELSE IF IND EQ 9  THEN CNT_IF_3_90
            ELSE IF IND EQ 10 THEN CNT_IF_2_90
            ELSE IF IND EQ 11 THEN CNT_IF_1_90
            ELSE IF IND EQ 12 THEN CNT_IF_0_90
            ELSE                   CNT_IF_YTD_90;
DATEFLD  /A10V = IF IND EQ 1  THEN '&CNT_IF_3_65.EVAL'
            ELSE IF IND EQ 2  THEN '&CNT_IF_2_65.EVAL'
            ELSE IF IND EQ 3  THEN '&CNT_IF_1_65.EVAL'
            ELSE IF IND EQ 4  THEN '&CNT_IF_0_65.EVAL'
            ELSE IF IND EQ 5  THEN '&CNT_IF_3_80.EVAL'
            ELSE IF IND EQ 6  THEN '&CNT_IF_2_80.EVAL'
            ELSE IF IND EQ 7  THEN '&CNT_IF_1_80.EVAL'
            ELSE IF IND EQ 8  THEN '&CNT_IF_0_80.EVAL'
            ELSE IF IND EQ 9  THEN '&CNT_IF_3_90.EVAL'
            ELSE IF IND EQ 10 THEN '&CNT_IF_2_90.EVAL'
            ELSE IF IND EQ 11 THEN '&CNT_IF_1_90.EVAL'
            ELSE IF IND EQ 12 THEN '&CNT_IF_0_90.EVAL'
            ELSE                   '&CNT_IF_YTD_90.EVAL';
END
TABLE FILE REPORT1
PRINT PIVOTFLD
BY DIVISION
BY REGION
BY STATE
BY DATEFLD
BY MEASTYPE
WHERE REPORT_TAB NE ''
ON TABLE HOLD AS PIVOTED FORMAT ALPHA
END
-RUN

FILEDEF PIVOTED DISK NET_CAP_RPT/PIVOTED.ftm (APPEND
-PIVOTCOL

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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
Hi Martin,

Sure I will try that but one question I have...
How can I build the loop for the other records to pivot the 13 columns from the HOLD file as the percentage values are not static and the data keeps changing.

Please correct me if Iam wrong.

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Virtuoso
posted Hide Post
Why your data should change ?

When you produce a report/graph, you display the data as is at that moment.
If you want to see if the data has changed, you run the report again and then you could have a different result (compare both reports).


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
Hi,

Okay..I got it Smiler
But when you say to build the loop for the other records to pivot the 13 columns from the HOLD file ...which records do you refer to? are these the percentages or something else?

Once again sorry for troubling you Frowner
Please help...


Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report This Post
Virtuoso
posted Hide Post
From your other post you have displayed the following about your data:
This is the output which I get :
REPORT_TAB 	DIVISION 	REGION 	STATE 	DATA 	CNT_IF_3_65 	CNT_IF_2_65 	CNT_IF_1_65 	CNT_IF_0_65 	CNT_IF_3_80 	CNT_IF_2_80 	CNT_IF_1_80 CNT_IF_0_80 	CNT_IF_3_90 	CNT_IF_2_90 	CNT_IF_1_90 	CNT_IF_0_90 	CNT_IF_YTD_90 
. 		. 		. 	. 	Date 	27-JAN-17 	10-FEB-17 	24-FEB-17 	10-MAR-17 	27-JAN-17 	10-FEB-17 	24-FEB-17 	10-MAR-17 	27-JAN-17 	10-FEB-17 	24-FEB-17 	10-MAR-17 	YTD AVG 2017	
ACCESS 		EAST 		E 	AL 	% Congested w/o PE 5.16% 5.67% 5.62% 5.28% 1.93% 2.33% 2.04% 2.10% 1.15% 1.09% 0.95% 1.08% 1.09% 


What I understand from that is when REPORT_TAB EQ ' ' the record contain the dates otherwise they are data records with the percentage per DIVISION/REGION/STATE.
So your first read (outside the loop) is for the date record : REPORT_TAB EQ ''
Following reads (innner loop) are for percentage records : REPORT_TAB NE '' or REPORT_TAB EQ 'ACCESS'

To be able to produce a report/graph you first need to understand and analyse your data.
I may be wrong, but according to information that I have and your data understanding, the sample code that I provided may answer your need. Look at it closely.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
Hi,

Thanks a lot Martin Smiler
Finally,to get the graph as posted in the image in the earlier post, can I do something like this ?

GRAPH FILE PIVOTED
SUM PIVOTFLD
BY DATEFLD
BY REGION
END
-RUN

Please let me know.

Regards,
IP
 
Posts: 270 | Registered: October 30, 2014Report This Post
Virtuoso
posted Hide Post
You should probably have to do something such as:
GRAPH FILE PIVOTED
SUM PIVOTFLD
BY DATEFLD
BY MEASTYPE NOPRINT
BY REGION
... <other graph formatting/attributes>
END
-RUN

But I suggest to use the GUI. It may help to format the way you want.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
Hi Martin,

Thanks a ton for all your help !!
I will use the GUI and you are a rockstar Smiler

Once again thanks a million!!

Regards,
IP


Webfocus 8105 Developer studio,Windows 7,HTML,Excel,PDF,Text,Infoassist,Graph,AHTML
 
Posts: 270 | Registered: October 30, 2014Report 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]How to convert the multiple fields from individual column to a single column

Copyright © 1996-2020 Information Builders