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,

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

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

Kofi

Kofi, that's very interesting! I've never used nor knew about the PUTDDREC function. I can see its usefulness. Thanks for the education.

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

Kofi

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

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.

Hi,

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

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

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,

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

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).

Hi,

Okay..I got it
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
Please help...


Regards,
IP

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.

Hi,

Thanks a lot Martin
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

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.

Hi Martin,

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

Once again thanks a million!!

Regards,
IP