Focal Point Banner


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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Turning row data into columns

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Turning row data into columns
 Login/Join
 
manish from curaspan
Member
posted
I have a report that needs to be able break out comma separated data that is in a row and do a query based on these which will ultimately produce a report where the data is broken out into rows.

For example if this is the source
  
   id      data
   --      ----
   test   1,2,3


This is the reference
  
   id      data
   --      ----
   1       ref 1 
   2       ref 2
   3       ref 3


Would produce a report like
  
 test   ref 1
 test   ref 2
 test   ref 3


I can't quite figure out how to do this in FOCUS is there a way?

If I was doing this in JAVA I would be able to use Hashtables and arrays to gather information and merge them.
Can I send the output of the first query to JAVA and then use its output to generate the second query as well as the report?

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 16 | Registered: September 12, 2012Report This Post
Vivian
Platinum Member
posted Hide Post
Perhaps you could look at the user written subroutines and use those to parse out the row - You will be able to find the data separated by commas and put those into discrete fields --- check it out...of course, I am certain you will discover other ways as well.

Good luck


Vivian Perlmutter
Aviter, Inc.


WebFOCUS Keysheet Rel. 8.0.2
(Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2
Focus since 1982
WebFOCUS since the beginning
Vivian@aviter.com

 
Posts: 191 | Location: Henderson, Nevada | Registered: April 29, 2003Report This Post
Danny-SRL
Virtuoso
posted Hide Post
What is the maximum number of commas in your data field?
What is the maximum size of each number?


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, 2006Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Assumptions.
Your file:
  
testa   1,2,3
testb   12
testc   123,2,4
testd   1,2,3,4,5,1234,134,13,10

The Master for this file
  
FILENAME=MANISH, SUFFIX=FIX, 
DATASET=FOCALPOINT/MANISH.TXT, $
 SEGNAME=MANISH, SEGTYPE=S0
  FIELDNAME=TNAME, FORMAT=A8, ACTUAL=A8, $
  FIELDNAME=TDATA, FORMAT=A30, ACTUAL=A30, $

Maximum number of values: 10
Maximum length of each number: 5

The fex to split the TDATA field:
  
-* File manish.fex
-SET &MAXN=10;
-SET &MAXS=5;
DEFINE FILE MANISH
-REPEAT #SPLIT FOR &I FROM 1 TO &MAXN;
NUM&I / I&MAXS.S = EDIT(GETTOK(TDATA, 30, &I, ',', &MAXS, 'A&MAXS'));
-#SPLIT

END
TABLE FILE MANISH
PRINT 
-REPEAT #P FOR &I FROM 1 TO &MAXN;
NUM&I 
-#P
BY TNAME
ON TABLE SAVE AS MTEMP 
END
-RUN


The MTEMP master:
  
FILENAME=MTEMP   , SUFFIX=FIX  
  SEGMENT=MTEMP, SEGTYPE=S0
    FIELDNAME=TNAME, ALIAS=E01, USAGE=A8, ACTUAL=A08, $
  SEGMENT=MDATA, PARENT=MTEMP, OCCURS=VARIABLE, $
    FIELDNAME=NUM, ALIAS=E02, USAGE=I5S, ACTUAL=A05, $


The output from MTEMP:
TABLE FILE MTEMP
PRINT *
IF NUM NE 0
END

From there you can join each value to the reference table.

Good luck!


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, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Turning row data into columns

Copyright © 1996-2020 Information Builders