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,

Emily,

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

Eric

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

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

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,

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

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?

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,

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

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.

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
Emily

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)