Focal Point
[CLOSED] Splitting a column value in webfocus

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6767002576

March 19, 2015, 12:14 PM
Bharathi
[CLOSED] Splitting a column value in webfocus
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>,
March 19, 2015, 12:54 PM
Ramkumar - Webfous
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?


Thanks,

Ramkumar.
WebFOCUS/Tableau
Webfocus 8 / 7.7.02
Unix, Windows
HTML/PDF/EXCEL/AHTML/XML/HTML5
March 19, 2015, 12:55 PM
Bharathi
Yes, Thats the value of col1.


WebFOCUS 8.1.05
Windows, All Outputs
March 19, 2015, 06:55 PM
njsden
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




Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
March 20, 2015, 04:45 AM
Bharathi
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 !
March 21, 2015, 06:57 AM
George Patton
Check out this recent post using OCCURS variable:

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


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
March 22, 2015, 03:36 AM
Danny-SRL
I would use an alternate master instead of MacGuyver.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

March 23, 2015, 02:38 AM
Danny-SRL
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



Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

March 23, 2015, 02:54 PM
Bharathi
It didnt run. Error near EX -LINES.


WebFOCUS 8.1.05
Windows, All Outputs
March 24, 2015, 12:56 AM
Rifaz
quote:
It didnt run. Error near EX -LINES.

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


-Rifaz

WebFOCUS 7.7.x and 8.x
March 24, 2015, 07:07 AM
Bharathi
ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key FRCSTDAT.fex

This is the error..
March 24, 2015, 07:41 AM
Rifaz
check all the necessary files required to run the report is published or not.


-Rifaz

WebFOCUS 7.7.x and 8.x
March 24, 2015, 12:13 PM
Danny-SRL
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



Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

March 26, 2015, 12:22 AM
Dan Satchell
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,


WebFOCUS 7.7.05
March 27, 2015, 03:05 PM
Bharathi
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.