As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. New TIBCO Community Coming Soon In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch. What You Need to Know about Our New Community We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion. During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.
Focal Point    Focal Point Forums    WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Splitting a column value in webfocus

 Go Search Notify Tools
 [CLOSED] Splitting a column value in webfocus
Gold member
 posted March 19, 2015 12:14 PM
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.

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

 Posts: 56 | Registered: January 13, 2015 IP
Guru
 posted March 19, 2015 12:54 PM Hide Post
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

 Posts: 394 | Location: Chennai | Registered: December 02, 2009 IP
Gold member
 posted March 19, 2015 12:55 PM Hide Post
Yes, Thats the value of col1.

WebFOCUS 8.1.05
Windows, All Outputs

 Posts: 56 | Registered: January 13, 2015 IP
Virtuoso
 posted March 19, 2015 06:55 PM Hide Post
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
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.

 Posts: 1533 | Registered: August 12, 2005 IP
Gold member
 posted March 20, 2015 04:45 AM Hide Post
Hi njsden !
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..

 Posts: 56 | Registered: January 13, 2015 IP
Master
 posted March 21, 2015 06:57 AM Hide Post
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

 Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010 IP
Virtuoso
 posted March 22, 2015 03:36 AM Hide Post
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

 Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006 IP
Virtuoso
 posted March 23, 2015 02:38 AM Hide Post
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

 Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006 IP
Gold member
 posted March 23, 2015 02:54 PM Hide Post
It didnt run. Error near EX -LINES.

WebFOCUS 8.1.05
Windows, All Outputs

 Posts: 56 | Registered: January 13, 2015 IP
Guru
 posted March 24, 2015 12:56 AM Hide Post
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

 Posts: 406 | Location: India | Registered: June 13, 2013 IP
Gold member
 posted March 24, 2015 07:07 AM Hide Post
ERROR: ERROR_MR_FEX_NOT_FOUND Can't create item object based on provided item key FRCSTDAT.fex

This is the error..

 Posts: 56 | Registered: January 13, 2015 IP
Guru
 posted March 24, 2015 07:41 AM Hide Post
check all the necessary files required to run the report is published or not.

-Rifaz

WebFOCUS 7.7.x and 8.x

 Posts: 406 | Location: India | Registered: June 13, 2013 IP
Virtuoso
 posted March 24, 2015 12:13 PM Hide Post
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

 Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006 IP
Virtuoso
 posted March 26, 2015 12:22 AM Hide Post
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

 Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007 IP
Gold member
 posted March 27, 2015 03:05 PM Hide Post
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.

 Posts: 56 | Registered: January 13, 2015 IP