Hi All,
I have two columns of data types A255v and P20 with the data below.
I need to split the value in col1 into multiple values like below.
hold file H1
col1 col2
1005560=1 1005555=1 1005597=1 1005549=1 1005591=1 1005543=1 1005585=1 1772010

Required output
col1 col2
1005560 1772010
1005555 1772010
1005597 1772010
1005549 1772010
1005591 1772010
1005543 1772010
1005585 1772010

Please let me know if you need any other details.
Its really urgent!Please help!

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

quote:

1005560=1 1005555=1 1005597=1 1005549=1 1005591=1 1005543=1 1005585=1

Is this the whole string in one row of Column 1?

Or whats it?

Yes, Thats the value of col1.

Using the McGyver technique is an easy way to get "n" rows out of a single one which seems to be what you need. As for parsing COL1 and obtaining each individual component you can use GETTOK. Please see example below using the CAR table to illustrate the concept.

-* Simulate the record you have
DEFINE FILE CAR
COL1/A255 WITH CAR = '1005560=1 1005555=1 1005597=1 1005549=1 1005591=1 1005543=1 1005585=1';
COL2/P20 WITH CAR = 1772010;
END
TABLE FILE CAR
PRINT COL1 AND COL2
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS HDATA
END
-RUN

-* Use McGyver technique to make up 7 rows
FILEDEF FSEQ DISK fseq.mas
-RUN
-WRITE FSEQ NOCLOSE $$$$$$$$
-WRITE FSEQ NOCLOSE FILE=FSEQ, SUFFIX=FIX
-WRITE FSEQ NOCLOSE SEGNAME=SEG1, SEGTYPE=S0
-WRITE FSEQ NOCLOSE FIELD=CONTROL, BLANK , A1, A1, $
-WRITE FSEQ NOCLOSE SEGNAME=SEG2, SEGTYPE=S0, PARENT=SEG1, OCCURS=VARIABLE
-WRITE FSEQ NOCLOSE FIELD=1CHAR, 1CHAR, A1, A1, $
-WRITE FSEQ NOCLOSE FIELD=COUNTER, ORDER, I4, I4, $
-CLOSE FSEQ
-RUN

-* Join HOLD to "McGyver" file and obtain a token from COL1 for each "row"
JOIN CLEAR *
JOIN LINK WITH COL1 IN HDATA TO CONTROL IN FSEQ AS J0

-*
DEFINE FILE HDATA
LINK/A1 = '$';
NEWCOL/A7=EDIT(GETTOK(COL1, 255, COUNTER, ' ', 9, 'A9'), '9999999');
END

TABLE FILE HDATA
PRINT
      COUNTER NOPRINT
	  NEWCOL AS COL1
	  COL2
WHERE COUNTER LE 7
END
-RUN

Hi njsden !
Thanks so much for your reply..
Your technique worked..But the values in col1 are not fixed. Some rows have 15 values, some have 3 or 4 or 10 rows..how can we make it dynamic?

Others.. Please post if you have any other techniques..

Thanks in advance !

Check out this recent post using OCCURS variable:

http://forums.informationbuild...677099476#5677099476

I would use an alternate master instead of MacGuyver.

Example:

  
-* File bharati01.fex
-* Alternate master
-* Simulate the record you have
DEFINE FILE CAR
COL1/A250 = DECODE COUNTRY(ENGLAND '1005560=1 1005555=1 1005597=1 1005549=1 1005591=1 1005543=1 1005585=1 '
                           FRANCE  '1006660=1 1006655=1 1006697=1 '
                           JAPAN   '1007770=1 1007775=1 1007777=1 1008860=1 1008855=1 1008897=1 1000060=1 1000055=1 1000097=1 ' ELSE ' ');
COL2/P20 = DECODE COUNTRY ( ENGLAND 1772010 FRANCE 1772050 JAPAN 1773210 ELSE 0);
END
TABLE FILE CAR
PRINT  COL2 COL1
IF COL2 NE 0
ON TABLE SAVE AS HDATA
END
-RUN
EX -LINES 7 EDAPUT MASTER,HDATA,C,MEM
FILENAME=HDATA, SUFFIX=FIX
SEGNAME=HDATA, SEGTYPE=S0
FIELDNAME=COL2, FORMAT=P20, ACTUAL=A20, $
SEGNAME=COL1VAL, PARENT=HDATA, OCCURS=VARIABLE
FIELDNAME=COL1, FORMAT=A10, ACTUAL=A10, $
FIELDNAME=CLIST, ALIAS=ORDER, FORMAT=I3, ACTUAL=I4, $
-RUN
TABLE FILE HDATA
PRINT COL1 COL2
IF COL1 NE ' '
END

It didnt run. Error near EX -LINES.

quote:

It didnt run. Error near EX -LINES.

It doesn't say, what error you're getting? BTW, it works.

ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key FRCSTDAT.fex

This is the error..

check all the necessary files required to run the report is published or not.

quote:

ERROR: ERROR_MR_FEX_NOT_FOUND

Bharathi,
It seems you are running under MRE or BID.
In this case, you might not have access to the internal procedure -LINES. So try:

  
DEFINE FILE CAR
COL1/A250 = DECODE COUNTRY(ENGLAND '1005560=1 1005555=1 1005597=1 1005549=1 1005591=1 1005543=1 1005585=1 '
                           FRANCE  '1006660=1 1006655=1 1006697=1 '
                           JAPAN   '1007770=1 1007775=1 1007777=1 1008860=1 1008855=1 1008897=1 1000060=1 1000055=1 1000097=1 ' ELSE ' ');
COL2/P20 = DECODE COUNTRY ( ENGLAND 1772010 FRANCE 1772050 JAPAN 1773210 ELSE 0);
END
TABLE FILE CAR
PRINT  COL2 COL1
IF COL2 NE 0
ON TABLE SAVE AS HDATA
END
-RUN
FILEDEF HMAS DISK HDATA.MAS
-RUN
-WRITE HMAS FILENAME=HDATA, SUFFIX=FIX
-WRITE HMAS SEGNAME=HDATA, SEGTYPE=S0
-WRITE HMAS FIELDNAME=COL2, FORMAT=P20, ACTUAL=A20, $
-WRITE HMAS SEGNAME=COL1VAL, PARENT=HDATA, OCCURS=VARIABLE
-WRITE HMAS FIELDNAME=COL1, FORMAT=A10, ACTUAL=A10, $
-WRITE HMAS FIELDNAME=CLIST, ALIAS=ORDER, FORMAT=I3, ACTUAL=I4, $
-RUN
TABLE FILE HDATA
PRINT COL1 COL2
IF COL1 NE ' '
END

Here's another approach. I borrowed from Daniel's method with the CAR file to create the initial H1 hold file. This approach goes through the H1 file up to 30 times (25 or 26 times might be enough) and pulls subsequent values from col1 each time, then appends those values to new file H2. It exits the -REPEAT loop when all values in col1 have been processed.

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-*
DEFINE FILE CAR
 COL1/A250V = DECODE COUNTRY ('ENGLAND'   '1005560=1 1005555=1 1005597=1 1005549=1 1005591=1 1005543=1 1005585=1'
                              'FRANCE'    '2005560=1 2005555=1 2005597=1 2005549=1 2005591=1 2005543=1 2005585=1 2005560=1 2005559=1 2005598=1 2005547=1 2005596=1 2005545=1 2005584=1'
                              'JAPAN'     '3007770=1 3007775=1 3007777=1 3008860=1 3008855=1 3008897=1 3000060=1 3000055=1 3000097=1'
                              'W GERMANY' '4005560=1 4005555=1 4005597=1'
                              ELSE        '');
 COL2/P20   = DECODE COUNTRY ('ENGLAND'   1772010
                              'FRANCE'    2772010
                              'JAPAN'     3772010
                              'W GERMANY' 4772010
                               ELSE       0 );
END
-*
TABLE FILE CAR
 PRINT
  COL1
  COL2
 WHERE (COL2 NE 0);
 ON TABLE HOLD AS H1
END
-RUN
-*
-REPEAT ENDREPEAT1 FOR &I FROM 1 TO 30
DEFINE FILE H1
 INVAL&I/A9 = GETTOK(COL1,255,&I,' ',9,'A9');
 VAL/A7 = EDIT(INVAL&I,'9999999$$');
END
-*
TABLE FILE H1
 PRINT
  VAL  AS 'COL1'
  COL2
 WHERE (VAL NE '');
 ON TABLE HOLD AS H2
END
-*
-RUN
-IF (&LINES EQ 0) GOTO EXITREPEAT1 ;
-IF (&I GT 1) GOTO ENDREPEAT1 ;
FILEDEF H2 DISK H2.FTM (APPEND
-RUN
-*
-ENDREPEAT1
-EXITREPEAT1
-*
TABLE FILE H2
 PRINT *
END

This message has been edited. Last edited by: Dan Satchell,

Danny and Dan ..
Thank you so much for response!
Will check how this works for me..
I had used SQL in my code for now.