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     Compute Field - How do I write this expression?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Compute Field - How do I write this expression?
 Login/Join
 
Gold member
posted
I am trying to write a simple expression in my compute expression builder and I cannot figure out how to write it, it keeps telling me it is not a legal expression. I tried with the Parentheses and without. Can someone help me out?

(IF POAGE GT '0' AND LE '30' THEN '0-30' )
ELSE
(IF POAGE GT '30' AND LE '60' THEN '31-60' )
ELSE
(IF POAGE GT '60' AND LE '90' THEN '61-90' )
ELSE
(IF POAGE GT '90' AND LE '120' THEN '91-120' )
ELSE
(IF POAGE GT '120' AND LE '150' THEN '121-150' )
ELSE
(IF POAGE GT '150' THEN '150+')


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Platinum Member
posted Hide Post
Remove the parentheses and use ELSE IF. Make sure to end with a ; as well.


dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat
 
Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006Report This Post
Gold member
posted Hide Post
quote:
Originally posted by .eric:
Remove the parentheses and use ELSE IF. Make sure to end with a ; as well.


Like this? Still does not work.

IF POAGE GT '0' AND LE '30' THEN '0-30'
ELSE
IF POAGE GT '30' AND LE '60' THEN '31-60'
ELSE
IF POAGE GT '60' AND LE '90' THEN '61-90'
ELSE
IF POAGE GT '90' AND LE '120' THEN '91-120'
ELSE
IF POAGE GT '120' AND LE '150' THEN '121-150'
ELSE
IF POAGE GT '150' THEN '150+'


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Platinum Member
posted Hide Post
I assuming you're storing that into an alpha right correct?

Also I think you need to move the ' around the numbers in your IF statement, if they are number values webfocus will error.


dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat
 
Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006Report This Post
Gold member
posted Hide Post
quote:
Originally posted by .eric:
I assuming you're storing that into an alpha right correct?

Also I think you need to move the ' around the numbers in your IF statement, if they are number values webfocus will error.


Yes I have it set as an A20V format.

What ' should I remove? Around the actual values I am searching for? Or around the output? (Ex. 0-30)


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Gold member
posted Hide Post
Tried this.... still no go.

IF POAGE GT 0 AND LE 30 THEN '0-30'
ELSE
IF GT 30 AND LE 60 THEN '31-60'
ELSE
IF GT 60 AND LE 90 THEN '61-90'
ELSE
IF GT 90 AND LE 120 THEN '91-120'
ELSE
IF GT 120 AND LE 150 THEN '121-150'
ELSE
IF GT 150 THEN '150+'


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Platinum Member
posted Hide Post
Just noticed, should be:

IF POAGE GT 0 AND POAGE LE 30 THEN '0-30'

Need POAGE on both sides of the AND.


dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat
 
Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006Report This Post
Gold member
posted Hide Post
Ok I entered it like below and it took it, but when I ran the report everything came back as 150+ days. I know that is not right, why is it thinking everything is over 150 days?


IF POAGE GT 0 AND POAGE LE 30 THEN '0-30'
ELSE
IF POAGE GT 30 AND POAGE LE 60 THEN '31-60'
ELSE
IF POAGE GT 60 AND POAGE LE 90 THEN '61-90'
ELSE
IF POAGE GT 90 AND POAGE LE 120 THEN '91-120'
ELSE
IF POAGE GT 120 AND POAGE LE 150 THEN '121-150'
ELSE
IF POAGE GT 150 THEN '150+'


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Platinum Member
posted Hide Post
That might be hard to determine w/o looking at the data. Is POAGE an integer or decimal field?


dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat
 
Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006Report This Post
Gold member
posted Hide Post
POAGE is a defined field, D5 is the format.

Here are my defines:

DEFINE FILE T_PROD_PO
DATETODAY/HYYMDS=HGETC(10, DATETODAY);
POAGE/D5=HDIFF(DATETODAY, ORDERDATE, 'DAY', POAGE);
END


Here is my report code:

TABLE FILE T_PROD_PO
SUM
CNT.PONUM AS 'COUNT,PONUM'
BY ACCOUNTNUM
BY TOTAL COMPUTE POAGEGROUP/A20V = IF POAGE GT 0 AND POAGE LE 30 THEN '0-30'
ELSE
IF POAGE GT 30 AND POAGE LE 60 THEN '31-60'
ELSE
IF POAGE GT 60 AND POAGE LE 90 THEN '61-90'
ELSE
IF POAGE GT 90 AND POAGE LE 120 THEN '91-120'
ELSE
IF POAGE GT 120 AND POAGE LE 150 THEN '121-150'
ELSE
IF POAGE GT 150 THEN '150+';
WHERE (( ACCOUNTNUM EQ '650126' OR '650127' OR '651000' ) AND ( T_PROD_PO.STATUS NE 'CAN' )) AND (( RECEIPTS EQ 'NONE' ) AND ( TOTALCOST NE .00 )) AND ( T_PROD_WORKORDER.STATUS NE 'CAN' OR 'CLOSE' );


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Platinum Member
posted Hide Post
quote:
BY TOTAL COMPUTE POAGEGROUP/A20V


This is taking the total of POAGE and using that in your compute, you probably want to change this compute to go into your SUM section so it will run for each account number.


dev: WF 7.6.5 w/IIS + Tomcat

prod: WF 7.6.5 w/IIS + Tomcat
 
Posts: 153 | Location: Chicago, IL | Registered: February 24, 2006Report This Post
Gold member
posted Hide Post
quote:
Originally posted by .eric:
quote:
BY TOTAL COMPUTE POAGEGROUP/A20V


This is taking the total of POAGE and using that in your compute, you probably want to change this compute to go into your SUM section so it will run for each account number.


Still doesn't work when I do it like this.

TABLE FILE T_PROD_PO
SUM
CNT.PONUM AS 'COUNT,PONUM'
COMPUTE POAGEGROUP/A20V = IF POAGE GT 0 AND POAGE LE 30 THEN '0-30' ELSE IF POAGE GT 30 AND POAGE LE 60 THEN '31-60' ELSE IF POAGE GT 60 AND POAGE LE 90 THEN '61-90' ELSE IF POAGE GT 90 AND POAGE LE 120 THEN '91-120' ELSE IF POAGE GT 120 AND POAGE LE 150 THEN '121-150' ELSE IF POAGE GT 150 THEN '150+';
BY ACCOUNTNUM


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Expert
posted Hide Post
What is the value of POAGE for ACCOUNTNUM? You are doing the calculation as part of a sort break and you will only get the last POAGE for each record for each account, assuming that there are more than one PONUM per account.

What you need to do is a PRINT first to see what the POAGE is for each PONUM per account.

That will explain what you are getting.

What you may want to do is make the compute a define and then sort on POAGEGROUP within account.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
Probablly now have to put the paranthesis back, however, I've found when checking to create ranges its best to work high to low. And I'm assuming you've verified POAGE's value.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
You could also make your code a little easier to read (and therefore maintain) by using FROM ... TO syntax -

IF POAGE FROM   0 TO  30 THEN    '0-30' ELSE
IF POAGE FROM  31 TO  60 THEN   '31-60' ELSE
IF POAGE FROM  61 TO  90 THEN   '61-90' ELSE
IF POAGE FROM  91 TO 120 THEN  '91-120' ELSE
IF POAGE FROM 121 TO 150 THEN '121-150' ELSE '150+';



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Still think he needs to do it as a define. The way it is currently coded, he will only get the last POAGE for the account sort group and I don't think that is what he wants.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
Rob

did you consider the use of group like

TABLE FILE PROD_PO
SUM
CNT.PONUM AS 'COUNT,PONUM'
BY PAOGE IN-GROUPS-OF 30 TOP 150
END


This should do it, the only problem might be the description.




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
Expert
posted Hide Post
Ginny,

I agree, but it does depend on whether he wants the define on the summed total of the days difference between order date and run date or individual values of course. Although why you'd want to sum the days difference by account I can't think.

If we knew more on what he was trying to do then we could make suggestions.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Rob,

Franks method is a very simple one and it will give a good output albeit with limited explanation of the grouping but it will give you what you want.

To achieve what I think you are asking, try this code. The first is Frank's method, the second gives the column titles that you are after -
DEFINE FILE GGORDER
  ORD_DATE/I8DMYY = ORDER_DATE;
  DAY_DIFF/I8     = DMY(ORD_DATE,31121997);
  ORD_AGE/A12     = IF DAY_DIFF FROM   0 TO 100 THEN '0-100'   ELSE
                    IF DAY_DIFF FROM 101 TO 200 THEN '101-200' ELSE
                    IF DAY_DIFF FROM 201 TO 300 THEN '201-300' ELSE
                    IF DAY_DIFF FROM 301 TO 400 THEN '301-400' ELSE
                    IF DAY_DIFF FROM 401 TO 500 THEN '401-500' ELSE '501+';
END
TABLE FILE GGORDER
WRITE CNT.DAY_DIFF
BY PRODUCT_ID
ACROSS DAY_DIFF IN-GROUPS-OF 100 TOP 500
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END
-RUN
TABLE FILE GGORDER
WRITE CNT.ORD_AGE AS 'Counts'
   BY PRODUCT_ID  AS 'Product'
ACROSS ORD_AGE    AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Gold member
posted Hide Post
quote:
TABLE FILE PROD_PO
SUM
CNT.PONUM AS 'COUNT,PONUM'
BY PAOGE IN-GROUPS-OF 30 TOP 150
END


Thanks I used Frank's method and that will work just fine for me.

Thanks for everyone's help!


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Platinum Member
posted Hide Post
Rob M.
If you are insistent on getting the original code to work - then I'd recommend the foll., but personally I like Frank's and Tony's appproach much better:


IF ((POAGE GT '0') AND (POAGE LE '30'))
THEN '0-30'
ELSE IF ((POAGE GT '30') AND (POAGE LE '60'))
THEN '31-60'
ELSE IF ((POAGE GT '60') AND (POAGE LE '90'))
THEN '61-90'
ELSE IF ((POAGE GT '90') AND (POAGE LE '120'))
THEN '91-120'
ELSE IF ((POAGE GT '120') AND (POAGE LE '150'))
THEN '121-150'
ELSE IF (POAGE GT '150') THEN '150+'

Hope this helps.

Ira
wf538 aix 533


aix-533,websphere 5.1.1,apache-2.0,
wf 538(d), 537 (p),
==============
7.6.11 (t) aix 5312
websphere 6.1.19
apache 2.0
 
Posts: 195 | Registered: October 27, 2006Report This Post
Virtuoso
posted Hide Post
I also use Tony's (or actual your original method, but only when the groups are in different size like

0-250
251-350
351-500
500-1000
1001-2500
higher.




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
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Compute Field - How do I write this expression?

Copyright © 1996-2020 Information Builders