[Solved] BY TOTAL COMPUTE in a Multiverb Request [Solved]
I am attempting to sort by a computed field within a multiverb request in order to allow the user to sort by the highest (or lowest) ratio. This is what I am trying to do:
TABLE FILE CAR
SUM
COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY TOTAL HIGHEST COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY COUNTRY
SUM
COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST;
BY TOTAL HIGHEST COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY COUNTRY
BY TOTAL HIGHEST COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY CAR
END
If you try running this code you will get an error when WebFocus gets to the second BY TOTAL COMPUTE... line.
Am I understanding correctly that you can only have one BY TOTAL COMPUTE... line within a TABLE FILE request? If so, would there be an alternate approach to achieve this result?This message has been edited. Last edited by: barcher,
BARCHER App Studio 8.203 Windows
June 20, 2016, 04:25 PM
Ram Prasad E
Try below code. Are you looking something similar to this?
DEFINE FILE CAR
DEALER_COST_N/D12.2=DEALER_COST;
RETAIL_COST_N/D12.2=RETAIL_COST;
END
TABLE FILE CAR
SUM
COMPUTE RATIO_CNTRY/D12.2=DEALER_COST/RETAIL_COST; NOPRINT
BY TOTAL HIGHEST RATIO_CNTRY NOPRINT
BY COUNTRY
SUM
COMPUTE RATIO_CNTRY/D12.2 = RATIO_CNTRY;
COMPUTE RATIO_CAR/D12.2=DEALER_COST_N/RETAIL_COST_N;
BY TOTAL HIGHEST RATIO_CNTRY NOPRINT
BY COUNTRY
BY CAR
END
You cannot write BY TOTAL COMPUTE, just use the COMPUTEd field name. Also, if you give similar names to different COMPUTEs WF will always take the first occurence. And if you have similar formulae, the same will apply, meaning that you should redefine fields that reused in the lower level of the multi-set request. Something as follows:
DEFINE FILE CAR
DCOST_M=DEALER_COST;
RCOST_M=RETAIL_COST;
END
TABLE FILE CAR
SUM
COMPUTE RATIO_C/D12.4 = DEALER_COST / RETAIL_COST;
BY TOTAL HIGHEST RATIO_C NOPRINT
BY COUNTRY
SUM
COMPUTE RATIO_M/D12.4 = DCOST_M / RCOST_M;
BY TOTAL HIGHEST RATIO_C NOPRINT
BY COUNTRY
BY TOTAL HIGHEST RATIO_M NOPRINT
BY CAR
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
June 21, 2016, 12:39 PM
barcher
Ram,
Thank you for that suggestion. This worked!
To give you more context, I am building a parameterized self-service report where these names are parameters. I attempted this initially , but I must have misspelled the reference to the COMPUTEd field.
Daniel,
Thank you for the background. This definitely clears up my confusion.
BARCHER App Studio 8.203 Windows
June 21, 2016, 12:48 PM
barcher
Here is the modified code from the initial post:
TABLE FILE CAR
SUM
COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST; NOPRINT
BY TOTAL HIGHEST RATIO NOPRINT
BY COUNTRY
SUM
DEALER_COST NOPRINT
RETAIL_COST NOPRINT
COMPUTE RATIO/D12.2 = DEALER_COST / RETAIL_COST;
BY TOTAL HIGHEST RATIO NOPRINT
BY COUNTRY
BY TOTAL HIGHEST RATIO NOPRINT
BY CAR
END
BARCHER App Studio 8.203 Windows
June 22, 2016, 02:09 AM
Danny-SRL
barcher,
Very good. By invoking again the cost fields in the second request its COMPUTE is correctly calculated. Nonetheless, you should give it a different name so that the sort is correct.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF