Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Splitting a column value in webfocus
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Splitting a column value in webfocus
 Login/Join
 
Gold member
posted
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>,
 
Posts: 56 | Registered: January 13, 2015Reply With QuoteReport This Post
Guru
posted 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, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
Yes, Thats the value of col1.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 56 | Registered: January 13, 2015Reply With QuoteReport This Post
Virtuoso
posted 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
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.
 
Posts: 1533 | Registered: August 12, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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 !
 
Posts: 56 | Registered: January 13, 2015Reply With QuoteReport This Post
Master
posted 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, 2010Reply With QuoteReport This Post
Virtuoso
posted 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: 1941 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted 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: 1941 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
It didnt run. Error near EX -LINES.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 56 | Registered: January 13, 2015Reply With QuoteReport This Post
Guru
posted 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: 405 | Location: India | Registered: June 13, 2013Reply With QuoteReport This Post
Gold member
posted 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, 2015Reply With QuoteReport This Post
Guru
posted 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: 405 | Location: India | Registered: June 13, 2013Reply With QuoteReport This Post
Virtuoso
posted 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: 1941 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Virtuoso
posted 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, 2007Reply With QuoteReport This Post
Gold member
posted 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, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Splitting a column value in webfocus

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.