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     [Solved] Can WF treat a field value as code rather than a literal value?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[Solved] Can WF treat a field value as code rather than a literal value?
 Login/Join
 
Silver Member
posted
Hello,
I am working on a code puzzle and have come up with a dead end when researching the documentation and posts in this forum. I’m not sure if what I’m trying to do is possible in WF, but I feel that there should be a way somehow. I would appreciate any assistance even if that is just letting me know if it can’t be done.

I am trying to get WF to evaluate the value of a field as code rather than the field, much like using &variable.EVAL in a define will evaluate the Dialog Manager variable as code.

I am trying to match up the following tables:

CATEGORY_ID BUCKET_FIELDS
1 BUCKET_A + BUCKET_B + BUCKET_C
2 BUCKET_D + BUCKET_E

BUCKET_A BUCKET_B BUCKET_C BUCKET_D BUCKET_E
1 2 3 4 5


To get:
CATEGORY_ID BUCKET_FIELDS BUCKET_A BUCKET_B BUCKET_C BUCKET_D BUCKET_E
1 BUCKET_A + BUCKET_B + BUCKET_C 1 2 3 4 5
2 BUCKET_D + BUCKET_E 1 2 3 4 5

I then want to use a define to treat the value of BUCKET_FIELDS as a formula and calculate the amount of the fields listed in the variable, so that I come up with the following results:
CATEGORY_ID BUCKET_FIELDS BUCKET_A BUCKET_B BUCKET_C BUCKET_D BUCKET_E AMOUNT
1 BUCKET_A + BUCKET_B + BUCKET_C 1 2 3 4 5 6
2 BUCKET_D + BUCKET_E 1 2 3 4 5 9


DEFINE FILE THISFILE
AMOUNT/I3 = BUCKET_FIELDS;
END

However when I try the above define, I get the following error, because WF is treating the field BUCKET_FIELDS as a field:
(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
AMOUNT
While I can probably get the desired result from looping through each category and using a –READ to set the value of BUCKET_FIELDS to an amper variable, holding a table for each category and then matching them back up, I would like to see if a function is available that will tell WF to treat the field as a formula or code first. I hate to make my code more complicated that it should be.

I would appreciate any suggestions.

Thank you,
Emily Maxwell

This message has been edited. Last edited by: Emily Max,


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Gold member
posted Hide Post
Emily,

Can you post some of your code? What are the formats of the bucket fields?

Eric


Eric Woerle
WF 7.6.7 Reportting Server
ETL 7.6.10
Dev Studio 7.6.7
 
Posts: 95 | Registered: July 31, 2007Report This Post
Master
posted Hide Post
Hi Emily,

Regarding your 'Business Rules' table:
  
CATEGORY_ID BUCKET_FIELDS (Calculations)  
1           BUCKET_A + BUCKET_B + BUCKET_C
2           BUCKET_D + BUCKET_E


Is there a one to one relationship between the Category_Id and the Bucket_Field (calculation)?

How many different Category_Id's (calculation types) are there?

Regards,
Dave
 
Posts: 822 | Registered: April 23, 2003Report This Post
Silver Member
posted Hide Post
Thank you for your replies.

The field formats are as follows:

First Table
CATEGORY_ID - I4
BUCKET_FIELDS – A100

Second Table
BUCKET_A - I4
BUCKET_B - I4
BUCKET_C - I4
BUCKET_D - I4
BUCKET_E - I4


The values of the field BUCKET_FIELDS in the first table will be a formula that include the field names on the second table.

The second table will only have one record in it. That record will be matched to each record in the first table. There will be several records in the first table.


MATCH FILE FIRST_TABLE
SUM
BUCKET_FIELDS
BY ID
BY CAT_ID
RUN

FILE SECOND_TABLE
SUM
BUCKET_A
BUCKET_B
BUCKET_C
BUCKET_D
BUCKET_E
BY ID
AFTER MATCH HOLD AS ALLDATA OLD
END
-RUN


DEFINE FILE ALLDATA
AMOUNT/I4 = BUCKET_FIELDS; ????????
END

TABLE FILE ALLDATA
SUM
AMOUNT
BY CAT_ID
END


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Expert
posted Hide Post
quote:
DEFINE FILE ALLDATA
AMOUNT/I4 = BUCKET_FIELDS; ????????
END


Em, this statement is puzzling. You've said that the field BUCKET_FIELDS is A100.
So you can't define a field /I4 and set it to a field that is /A100.

Are you trying to create 2 different types of aggregates?
if so, then if you can get a file
ID
CAT_ID
BUCKET_A
BUCKET_B
BUCKET_C
BUCKET_D
BUCKET_E
then
DEFINE FILE thatfile
AMOUNT/I4=IF CAT_ID IS 1 THEN BUCKET_A+BUCKET_B+BUCKET+C ELSE
IF CAT_ID IS 3 THEN BUCKET_D+BUCKET+E;
END
..now
TABLE FILE thatfile
SUM AMOUNT BY CAT_ID
END
..should do it for you.
-s.

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




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Silver Member
posted Hide Post
I want to allow the report to be dynamic in the case that the data in bucket_fields changes. For example, CAT_ID 1 may have a BUCKET_FIELDS value of ‘BUCKET_A + BUCKET_B + BUCKET_C’ this week, but next week it could be ‘BUCKET_A + BUCKET_B’. I don’t want to hard code the formula, as this would force the report to be updated every time the bucket allocation changes. So what I’m looking for is a function that will take the field name BUCKET_FIELDS and replace it with the current field’s value ‘BUCKET_A + BUCKET_B + BUCKET_C’ and then calculate the value of field BUCKET_A + field BUCKET_B + field BUCKET_C. Is this possible?

Thank you,
Emily


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Expert
posted Hide Post
ohhhhh
so, how are you determining the formulae?
is there a file somewhere that says
week# cat_id# defintion_this_week
1       1      ABC
1       2      DE
2       1      AB
2       2      EF
..etc?

What's the source of this 'rule', each week?




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Silver Member
posted Hide Post
To update the formulas, we would change the data in the file FIRST_TABLE. There wouldn’t be any set rule that would determine when this might change, more at the whim of management. I want the report to be able to dynamically make the change without having to make the change in the report code. I am able to make it work using a –READ as follows:

-SET &totalbuckets = &RECORDS;
-SET &thisid = 1;
-REPEAT BUCKETLOOP FOR &thisid FROM 1 TO &totalbuckets;

-* get bucket
TABLE FILE FIRST_TABLE
PRINT
BUCKET_FIELDS
WHERE CAT_ID EQ &thisid
ON TABLE HOLD AS THISBUCKET FORMAT ALPHA
END
-RUN
-READ THISBUCKET &thisbucket.A100.
-RUN

-SET &holdfile = BUCKET|&thisid;

DEFINE FILE SECOND_TABLE
AMOUNT/D12.2 = &thisbucket.EVAL;
END

MATCH FILE FIRST_TABLE
BY ID
BY CAT_ID
WHERE CAT_ID EQ &thisid
RUN

FILE SECOND_TABLE
SUM
AMOUNT
BUCKET_A
BUCKET_B
BUCKET_C
BUCKET_D
BUCKET_E
BY ID
AFTER MATCH HOLD AS &holdfile OLD
END
-RUN

TABLE FILE &holdfile
PRINT *
END

-BUCKETLOOP
-EXIT


This changes the field value to an &variable which I can then use .EVAL. I can then match up each holdfile to create a final table with each CAT_ID and its corresponding AMOUNT. It just seems like there should be a WF function that will do this.

Thanks for everything,

Emily

This message has been edited. Last edited by: Emily Max,


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
<JG>
posted
I've made several assumptions about the layout of the data
such as BUCKET_FIELDS being a single contiguous field with no spaces.
but here is a basic solution.

 
FILEDEF MAS1 DISK CATEGORY.MAS (APPEND
FILEDEF MAS2 DISK BUCKET.MAS (APPEND
FILEDEF CATEGORY DISK CATEGORY.FTM (APPEND
FILEDEF BUCKET DISK BUCKET.FTM (APPEND
-RUN
-WRITE MAS1 FILENAME=CATEGORY    , SUFFIX=FIX     , $
-WRITE MAS1  SEGMENT=CATEGORY, SEGTYPE=S1, $
-WRITE MAS1  FIELDNAME=CATEGORY_ID, ALIAS=E01, USAGE=A4, ACTUAL=A4, $
-WRITE MAS1  FIELDNAME=BUCKET_FIELDS, ALIAS=E02, USAGE=A100, ACTUAL=A100, $
-*
-WRITE MAS2 FILENAME=BUCKET    , SUFFIX=FIX     , $
-WRITE MAS2  SEGMENT=BUCKET, SEGTYPE=S1, $
-WRITE MAS2  FIELDNAME=BUCKET_A, ALIAS=E01, USAGE=I4, ACTUAL=A4, $
-WRITE MAS2  FIELDNAME=BUCKET_B, ALIAS=E02, USAGE=I4, ACTUAL=A4, $
-WRITE MAS2  FIELDNAME=BUCKET_C, ALIAS=E03, USAGE=I4, ACTUAL=A4, $
-WRITE MAS2  FIELDNAME=BUCKET_D, ALIAS=E04, USAGE=I4, ACTUAL=A4, $
-WRITE MAS2  FIELDNAME=BUCKET_E, ALIAS=E05, USAGE=I4, ACTUAL=A4, $
-*
-WRITE CATEGORY 0001BUCKET_ABUCKET_BBUCKET_C
-WRITE CATEGORY 0002BUCKET_ABUCKET_BBUCKET_CBUCKET_DBUCKET_E
-WRITE BUCKET 00010001000100010001
-*
-SET &CATEGORY_ID = '0001';
-*
TABLE FILE CATEGORY
PRINT 
COMPUTE BUCKET_CALC/A120=TRIM('L', STRREP(100, BUCKET_FIELDS, 6, 'BUCKET', 7,'+BUCKET',120,'A120'), 120, '+', 1, 'A120');
COMPUTE BUCKET_COLS/A120= STRREP(100, BUCKET_FIELDS, 6, 'BUCKET', 7,' BUCKET',120,'A120') ;

WHERE CATEGORY_ID  EQ '&CATEGORY_ID';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-READ SAVE &BUCKET_CALC.A120. &BUCKET_COLS.A120.
DEFINE FILE BUCKET
CATEGORY_ID/A4 WITH BUCKET_A='&CATEGORY_ID';
END
TABLE FILE BUCKET
PRINT *
BY CATEGORY_ID
ON TABLE HOLD AS MYCAT
END
-RUN
JOIN CATEGORY_ID IN CATEGORY TO CATEGORY_ID IN MYCAT AS J1
TABLE FILE CATEGORY
SUM
COMPUTE AMOUNT/I9=
&BUCKET_CALC.EVAL ;
&BUCKET_COLS.EVAL
BY CATEGORY_ID
WHERE CATEGORY_ID EQ '&CATEGORY_ID'
END
 
Report This Post
Virtuoso
posted Hide Post
Emilie

We do something like this.
Created a table that holds the formulas like this.
I give an example based on the CAR

The database FORMULAS should have the following records (all fields are alpha)
DBNAAM  FIELDNAAM   FORMULA                  FORMAAT
CAR     DMARGIN     RCOST - DCOST            D12.2S
CAR     PMARGIN     RCOST/DCOST              D7.3%       


Now you can create a fex based on these records and include the result as a define in your final report.

DEFINE FILE FORMULAS
STRING/A100=FIELDNAAM||'/'||FORMAAT||'='||FORMULA||';'
END
TABLE FILE FORMULAS
PRINT STRING
WHERE DBNAAM EQ 'CAR';
ON TABLE HOLD AS DEFINCLU FORMAT ALPHA
END
-RUN
DEFINE FILE CAR
-INCLUDE DEFINCLU
...some other defines
END
TABLE FILE CAR
PRINT
  DMARGIN
  PMARGIN
BY COUNTRY
BY MODEL
BY CAR
END


This is the basic, I did not test this, there might be some problems with the quotes but it's a start.




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
Silver Member
posted Hide Post
Good morning,
I would like to thank everyone for your suggestions; I greatly appreciate your assistance. I am going to play around with the suggestions given, and mark this thread as solved.

Thank you and have a great day Smiler
Emily


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Platinum Member
posted Hide Post
The Basic Idea is to Write the lines of generated Code in a File (Code seen as Data)
And to make Focus consider those Lines of Data as Code through -INCLUDE or EX/EXEC . (change of dimension, somewhere ...)
Make sure that Dialog Manager (of the proper machine, when using DS) cannot bring the solution more easily.
Because using DM needs to understand how DM and Focus share time, but When you get it, DM is real simple thanks to the Echo facility ...
I often see DM as a (not so brilliant) boss sending Orders to an employee through a letter Basket.
Later on, the Employee (who can do many more things than the boss) read the order and Run to do it. But the details of the job have been specified by the Boss -who take all the benefit of the Job done ...
PS : Need synchronising when different tasks (-RUN)


Focus Mainframe 7.6.11
Dev Studio 7.6.11 and !!!
PC Focus, Focus for OS/2, FFW Six, MSO
 
Posts: 134 | Registered: November 06, 2007Report 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     [Solved] Can WF treat a field value as code rather than a literal value?

Copyright © 1996-2020 Information Builders