Hi all;
What's the BEST way to re-arrange rows (horizontal) to a Columns (Vertical)

Example:
ACCOUNT Sep-13 Oct-13 Nov-13
Income 999.99 999.99 999.99
Exp.. .... .... .....


TO
Date Account Amount
Sep-13 Income 999.99
Oct-13 Income 999.99
Nov-13 .. ...
Sep-13 Exp. 999.99
.. .. ..

Thanks

This message has been edited. Last edited by: <Kathryn Henning>,

Have a look for the Macgyver technique.

Thanks Waz..
But the Macgyver technique is repeating the rows..

FOCdev,
Could you clarify your data?
When you write:

  
ACCOUNT Sep-13 Oct-13 ...
Income  999.99 999.99 ...

are ACCOUNT and Sep-13 etc. fieldnames?
And Income, 999.99 values?

I figured it out..
Danny. I have data fields like Month1, Month2, Month3... and so on with other financial data in monthly buckets.. this was calculated data..
To use the same data as is, for reporting would be cumbersome, like using the headings to format the data..
For reporting purpose, so that its easier to format the report, i wanted to have this data in a Table format, so MONTH field will have 01 to 12 months values and so on..
Using Mac Tec, i repeated the rows 12 times and then using Define and COUNTER I created the MONTH field to dump each value.. following is the code.

Thanks WAZ, Danny..
others, if there is any other way, let me know..

Code
-* Use Mcgyver Technique to re-arrange the report data from rows to columns
-*Macgyver technique.
DEFINE FILE ITREND2
BLANK/A1 WITH RPTKEY = ' ';
COUNT/I1 WITH RPTKEY = 1;
END
-RUN
TABLE FILE ITREND2
PRINT
COMPUTE COUNTER/I2 = COUNT + COUNTER;
BY BLANK
ON TABLE HOLD AS FSEQ FORMAT FOCUS INDEX BLANK
-* report is for 12 months
IF RECORDLIMIT EQ 12
END
-RUN
JOIN BLANK WITH RPTKEY IN ITREND2 TO ALL BLANK IN FSEQ
-RUN
DEFINE FILE ITREND2
BLANK /A1 WITH RPTKEY = ' ';
END
-RUN
TABLE FILE ITREND2
PRINT COUNTER RPTKEY
GLRE_ACT_YR1
GLRE_ACT_YR2
GLRE_ACT_YR3
GLRE_ACT_YR4
GLRE_ACT_YR5
GLRE_ACT_YR6
GLRE_ACT_YR7
GLRE_ACT_YR8
GLRE_ACT_YR9
GLRE_ACT_YR10
GLRE_ACT_YR11
GLRE_ACT_YR12

GLRE_ACCT_MTH1
GLRE_ACCT_MTH2
GLRE_ACCT_MTH3
GLRE_ACCT_MTH4
GLRE_ACCT_MTH5
GLRE_ACCT_MTH6
GLRE_ACCT_MTH7
GLRE_ACCT_MTH8
GLRE_ACCT_MTH9
GLRE_ACCT_MTH10
GLRE_ACCT_MTH11
GLRE_ACCT_MTH12
BY RPTKEY
ON TABLE HOLD AS ITREND3 FORMAT FOCUS INDEX RPTKEY
END
-RUN
DEFINE FILE ITREND3
MONTH/A2=IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 1 THEN GLRE_ACCT_MTH1 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 2 THEN GLRE_ACCT_MTH2 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 3 THEN GLRE_ACCT_MTH3 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 4 THEN GLRE_ACCT_MTH4 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 5 THEN GLRE_ACCT_MTH5 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 6 THEN GLRE_ACCT_MTH6 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 7 THEN GLRE_ACCT_MTH7 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 8 THEN GLRE_ACCT_MTH8 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 9 THEN GLRE_ACCT_MTH9 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 10 THEN GLRE_ACCT_MTH10 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 11 THEN GLRE_ACCT_MTH11 ELSE
IF RPTKEY EQ LAST RPTKEY AND COUNTER EQ 12 THEN GLRE_ACCT_MTH12 ELSE ' ';
END
-RUN
TABLE FILE ITREND3
PRINT COUNTER MONTH
GLRE_ACCT_MTH1
...
...

FOCdev,
Could you post your .mas file?
I think I might have something simpler with a solution based on an alternate master.

Data in (rows) is in a Hold file..
ALPHANUMERIC RECORD NAMED SAVE
0 FIELDNAME ALIAS FORMAT LENGTH
RPTKEY A19 19
GLRE_ACCT_NO E01 A8 8
GLRE_LOC1 E02 A6 6
GLRE_LOC2 E03 A6 6
..
GLRE_LOC12 E13 A6 6
GLRE_ACT_YR1 E14 A4 4
GLRE_ACT_YR2 E15 A4 4
..
GLRE_ACT_YR12 E25 A4 4
GLRE_ACCT_MTH1 E26 A2 2
GLRE_ACCT_MTH2 E27 A2 2
..
GLRE_ACCT_MTH12 E37 A2 2
MTD_AMT1 E38 D12 12
MTD_AMT2 E39 D12 12
..
MTD_AMT3 E40 D12 12
MTD_AMT12 E41 D12 12
..

If your output is to be rows, you can use the OVER command

e.g.

TABLE FILE CAR
PRINT COUNTRY
OVER CAR
OVER MODEL
END

OVER is for display only.. I used the Mac Tech and its working.. Thanks all

This is your master:

  
 RPTKEY A19 19
 GLRE_ACCT_NO E01 A8 8
 GLRE_LOC1 E02 A6 6
 GLRE_LOC2 E03 A6 6
 .. 
 GLRE_LOC12 E13 A6 6
 GLRE_ACT_YR1 E14 A4 4
 GLRE_ACT_YR2 E15 A4 4
 ..
 GLRE_ACT_YR12 E25 A4 4
 GLRE_ACCT_MTH1 E26 A2 2
 GLRE_ACCT_MTH2 E27 A2 2
 ..
 GLRE_ACCT_MTH12 E37 A2 2
 MTD_AMT1 E38 D12 12
 MTD_AMT2 E39 D12 12
..
 MTD_AMT3 E40 D12 12
 MTD_AMT12 E41 D12 12
..

Now if you arrange it thus:

  
 RPTKEY A19 19
 GLRE_ACCT_NO E01 A8 8
 GLRE_LOC1 E02 A6 6
 GLRE_ACT_YR1 E14 A4 4
 GLRE_ACCT_MTH1 E26 A2 2
 MTD_AMT1 E38 D12 12
 GLRE_LOC2 E02 A6 6
 GLRE_ACT_YR2 E14 A4 4
 GLRE_ACCT_MTH2 E26 A2 2
 MTD_AMT2 E38 D12 12
...
 GLRE_LOC12 E02 A6 6
 GLRE_ACT_YR12 E14 A4 4
 GLRE_ACCT_MTH12 E26 A2 2
 MTD_AMT12 E38 D12 12

you can create an alternate master:

  
FILENAME=SAVE, SUFFIX=FIX
SEGNAME=TOP, SEGTYPE=S0
 FIELDNAME=RPTKEY, USAGE=A19, ACTUAL=A19,$
 FIELDNAME=GLRE_ACCT_NO, USAGE=A8, ACTUAL=A8,$
SEGNAME=MONTHS, PARENT=TOP, OCCURS=VARIABLE
 FIELDNAME=GLRE_LOC, USAGE=A6, ACTUAL=A6,$
 FIELDNAME=GLRE_ACT_YR, USAGE=A4, ACTUAL=A4,$
 FIELDNAME=GLRE_ACCT_MTH, USAGE=A2, ACTUAL=A2,$
 FIELDNAME=MTD_AMT, USAGE=D12, ACTUAL=A12,$
 FIELDNAME=MONTH, ALIAS=ORDER, USAGE=I2, ACTUAL=I4,$

In this way you have transformed a horizontal list of fields into a repeating group of fields that can be arranged vertically. The last field is provided by WebFOCUS and gives you the order of the repeating group of fields.

This technique is called "Alternate Master".

Daniel - Clone of Macgyver

Daniel Thanks.. I have seen your posts on Alternate Master, technique I will try it.

Thanks