Hi
I HAVE 30 FIELDS AND 2 ROWS OF DATA IN MY TABLE, I WANT TO CONVERT ROWS VALUES INTO COLUMNS AND VICEVERSA
I USED OVER COMMAND BUT IAM UNABLE TO HOLD THE DATA IN MY REQUIRED FORMAT

i.e A1 A2 A3 A4 A5 A6 A7 .......
10 20 30 40 50 60 70 ......
12 13 14 15 16 17 18 .......

I WANT TO CONVERT THIS TABLE IN TO

A1 10 12
A2 20 13
A3 30 14
A4 40 15
A5 50 16
.
.
.
HELP ME IN THIS
THANKS
NAG.

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

I'm not sure what you need. Can you post the master for this table along with some code you used in your attempt to use over? This sounds like something where the McGuyver technique could be used but I'm not sure.

Hi GM
Basically iam tring to impliment TRANSPOSE functionality
i.e
My Table has 5 columns and 2 rows of data but i want to make it as 5 rows and 2 coloumns of data

Nag

Is the data in a flat file?

always two records, or can it be (much) more

Frank

Transpose is an excel command....

do you want to do this in excel?

Frank

Hi
The data source is Flat file and it has thousand records and just i want to impliment my functionality in my Report

so when you transpose you might get 1000 columns is that what you really want

Frank

Hi
yes i need 1000 columns

Good luck getting the MFD parser to work in a reasonable amount of time with 1000 columns in a table. I don't know if there is an actual limit to the number of fields but you're definitely going to notice a delay trying to continuously parse an MFD that big.

Actually my final hold file will have 3 rows and 40 columns of data which i want to change it to 3 columns and 40 rows

NAG, you haven't been consistent with your requirements, so I will assume the first requirement, "I HAVE 30 FIELDS AND 2 ROWS OF DATA IN MY TABLE..."

The OCCURS clause in the Master may do the trick.

WORKING EXAMPLE:

My assumptions are that the data is a flat file, is in the proper order and that there really is a difference between the two rows of data (row one A1..., row two 001...).

Master:

FILE = F001, SUFFIX=FIX, $

SEGNAME = SEG01, SEGTYPE=S0, OCCURS=30, $

FIELD = FLD001, ALIAS=E01, ACTUAL=A03, USAGE=A03, $

DEFINE FLD_TYPE/A1  = IF CHKFMT(3, FLD001, 'A$$', 'I1') EQ 0 THEN 'A' ELSE 'B';
DEFINE FLD_COUNT/I2 = IF FLD_TYPE EQ LAST FLD_TYPE THEN FLD_COUNT + 1 ELSE 1;

Focexec:

FILEDEF F001 DISK F001.TXT
-RUN

-WRITE F001 A1 A2 A3 A4 A5 B1 B2 B3 B4 B5 C1 C2 C3 C4 C5 D1 D2 D3 D4 D5 E1 E2 E3 E4 E5 F1 F2 F3 F4 F5
-WRITE F001 10 20 30 40 50 60 70 80 90 100110120130140150160170180190200210220230240250260270280290300

TABLE FILE F001
SUM
FLD001
BY FLD_COUNT NOPRINT
ACROSS FLD_TYPE NOPRINT
END
-RUN

this is inelegant , but works to get you this:
A1 10 12
A2 20 13
A3 30 14
A4 40 15
A5 50 16
A6 60 17
A7 70 18
from this input
A1 A2 A3 A4 A5 A6 A7
10 20 30 40 50 60 70
12 13 14 15 16 17 18
FILEDEF NAGDATA DISK D:\IBI\APPS\NAGDATA.TXT

FILEDEF NAGDATA2 DISK D:\IBI\APPS\NAGDATA2.TXT (APPEND
CMD ERASE D:\IBI\APPS\NAGDATA2.TXT
-SET &KOUNTER = 0 ;
-REPEAT end.loop 7 TIMES
-SET &KOUNTER = &KOUNTER + 1 ;
TABLE FILE NAGDATA PRINT F&KOUNTER
ON TABLE SAVE
END
-RUN
-READ SAVE &R1.A3 &R2.A3 &R3.A3
-WRITE NAGDATA2 &R1 &R2 &R3
-end.loop
where the master for NAGDATA is
FILE=NAGDATA ,SUFFIX=FIX
SEGNAME=NAGDATA,SEGTYPE=S01
FIELDNAME =F1 ,E01 ,A3 ,A03 ,$
FIELDNAME =F2 ,E02 ,A3 ,A03 ,$
FIELDNAME =F3 ,E03 ,A3 ,A03 ,$
FIELDNAME =F4 ,E04 ,A3 ,A03 ,$
FIELDNAME =F5 ,E05 ,A3 ,A03 ,$
FIELDNAME =F6 ,E06 ,A3 ,A03 ,$
FIELDNAME =F7 ,E07 ,A3 ,A03 ,$

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

Thaks all for Help
I Went with the Susannah advise and i landed safely.