Focal Point
Compute Field - How do I write this expression?
February 19, 2008, 03:41 PM
Rob M.Compute Field - How do I write this expression?
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
February 19, 2008, 03:43 PM
.ericRemove 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
February 19, 2008, 03:46 PM
Rob M.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
February 19, 2008, 03:48 PM
.ericI 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
February 19, 2008, 03:50 PM
Rob M.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
February 19, 2008, 03:52 PM
Rob M.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
February 19, 2008, 03:55 PM
.ericJust 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
February 19, 2008, 04:01 PM
Rob M.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
February 19, 2008, 04:04 PM
.ericThat 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
February 19, 2008, 04:11 PM
Rob M.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
February 19, 2008, 04:19 PM
.ericquote:
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
February 19, 2008, 04:38 PM
Rob M.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
February 19, 2008, 05:25 PM
GinnyJakesWhat 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
February 19, 2008, 05:26 PM
LeahProbablly 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
February 19, 2008, 06:02 PM
Tony AYou 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 | |
February 19, 2008, 06:04 PM
GinnyJakesStill 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
February 20, 2008, 04:28 AM
FrankDutchRob
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 |
February 20, 2008, 05:58 AM
Tony AGinny,
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 | |
February 20, 2008, 06:17 AM
Tony ARob,
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 | |
February 20, 2008, 09:38 AM
Rob M.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
February 20, 2008, 12:18 PM
iraRob 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
February 20, 2008, 12:28 PM
FrankDutchI 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 |