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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Values in one field
 Login/Join
 
FrankDutch
Virtuoso
posted
I have a "funny" database that has among others the following two fields

CLIENTCODE
PORTFOLIO

The first is a key field and gives no problems
the next is an A1000V field and holds in one comma delimited string all the stockcodes for that client.
Creating a single portfolio for one client works.
But now they want me to calculate the portfolio value per client and put the totals in a report.

I know I can do that by looping through the database, calculate the value as I did for one portfolio write that in an external file and move to the next.
But that is a unwanted solution (it's in fact how they do that now with some other tool)

Suggestions?




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Majid Jeddi
Guru
posted Hide Post
Hi,

There is a way how to do that through a stored procedure that will do all the calculation/parssing process and return data.
Your webfocus report will use then this stored procedure as a data source.
Please Send me the structure of your table and 2 records in it and i will try to write the stored proc fro you.

Regards.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Frank,

Hi. How is it back in Holland?

As for your problem. Do I understand correctly, that in your PORTFOLIO field you have something like:
code1,code2,code3, ... , coden
Then somewhere you also have the number of shares for each stock code and also somewhere else the value for each stockcode?
Is it correct that you want to isolate each code, join it to the value and to the number of shares?
If so, could you explain where/how all the data are held?


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
FrankDutch
Virtuoso
posted Hide Post
Majid, thanks in advange...

Code example for this.

CLIENT/A8
PORTFOIO/A8000V
010982 '938629','938630','938631','939154','939153','939152','02100084','02100083','02100079','02100078' 
011268 '02100078','02100079','02100083','02100084','02100383','02100384','02100432','02100433','02100455','0210045'


I broke up the second field for this example to the shown numbers, but in real there was a string with 43 values.

Danny

Holland is ok, bit rainy, but my holiday is over so I don't mind (the rest of the family does...)

The real world for this challange is a bit more complex, I symplified it for this question.
In fact the program should calculate the amount of management fee for a portfolio.
The field of codes here is a per client list of codes where the client does not have to pay management fee for.
This is per client different (don't ask me why, I just have to live with that fact)
Although many stcks come back in several portfolios, it's not clear when they charge and when they don't, so the list must be used.

The real portfolio is in an other tabel, with all the fileds you brought up.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Frank,

Fine. So you want to access each code separately, as if you had them in a column, with something like this (using your example)
  
CLIENT/A8 STOCK_CODE/A8
011268 '02100078'
011268 '02100079'
011268 '02100083'
...

Isn't this a task for McGuyver?


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
FrankDutch
Virtuoso
posted Hide Post
Yes
in fact the other question from VED is a bit of a combination of my problem.
If I was able to read the field with a new master with OCCURS=VARIABLE in combination with comaa delimited field lenght I would be happy.
i tested that but it does not work.
My fields have different length, but the max is 8 characters.
That might lead me to an other solution.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
PBrightwell
Master
posted Hide Post
Are the values all comma delimited? If they are you might try a GETTOK within a -REPEAT loop.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Tom Flynn
Expert
posted Hide Post
Hi Frank,,

OCCURS can be an actual numbered occurrence.

See if this helps you...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Frank,

Can this help?
  
FD.mas
FILENAME=FD    , SUFFIX=FIX,
 DATASET=C:\IBI\APPS\FOCALPOINT\FD.TXT, $
 SEGMENT=FD
 FIELDNAME=CLIENT, ALIAS=CL, USAGE=A8, ACTUAL=A8, $
 FIELDNAME=PORTFOLIO, ALIAS=PF, USAGE=A1000, ACTUAL=A1000, $


FD.txt
010982  '938629','938630','938631','939154','939153','939152','02100084','02100083','02100079','02100078' 
011268  '02100078','02100079','02100083','02100084','02100383','02100384','02100432','02100433','02100455','0210045'


-* File FD1.fex
JOIN BLANK WITH CLIENT IN FD TO BLANK IN FSEQ AS M_
DEFINE FILE FD
BLANK/A1 WITH CLIENT= ' ';
STOCK_CODE/A10=GETTOK(PORTFOLIO, 1000, COUNTER, ',', 10, 'A10');
END
TABLE FILE FD
PRINT STOCK_CODE
BY CLIENT BY COUNTER
IF STOCK_CODE 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, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders