Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WHERE clause on a computed field after an across.
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] WHERE clause on a computed field after an across.
 Login/Join
 
Member
posted
I'm calculating a difference between two fields under an across as such:

TABLE FILE MAZCV
SUM FIELD1
FIELD2
BY FIELD3
ACROSS FIELD4
COMPUTE TESTDIFF/I9 = C2 - C1;
END

This works fine. But I would like to filter on the computed field. For example, WHERE TESTDIFF LE 10.

TABLE FILE MAZCV
SUM FIELD1
FIELD2
BY FIELD3
ACROSS FIELD4
COMPUTE TESTDIFF/I9 = C2 - C1;
WHERE TOTAL TESTDIFF LE 10
END

It gives me a weird error message. Is there anyway to put a WHERE clause on a computed field after an across ?

This is the error message I get:

(FOC14069) SYNTAX ERROR ON LINE 1 AT '=' -- Literal expected

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


WF 7.6.4
 
Posts: 21 | Registered: January 27, 2004Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Try this:

 

TABLE FILE EMPDATA
SUM
     SALARY
BY DEPT
ACROSS DIV
COMPUTE TESTDIFF/D12.2 = C2 - C1;
ON TABLE HOLD
END		
	 
TABLE FILE HOLD
PRINT
     TESTDIFF
BY DEPT
WHERE TESTDIFF GT 0
END
 


Try this with and without the WHERE.

Regards,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Thanks John.

The problem with your example is that you're losing the across field. No where in the output do you see the DIV.

Basically I want the exact same output of your first query but only show the results where TESTDIFF > 0


WF 7.6.4
 
Posts: 21 | Registered: January 27, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Cribbing from John's code, try this:
DEFINE FILE EMPDATA
SALINCR/D12.2=SALARY * 1.05;
END
TABLE FILE EMPDATA
SUM
     SALINCR SALARY
COMPUTE TESTDIFF/D12.2 = C1 - C2;
BY DEPT
BY DIV
WHERE TOTAL TESTDIFF GE 5000
ON TABLE HOLD
END			 
TABLE FILE HOLD
SUM TESTDIFF
BY DEPT
ACROSS DIV
END


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, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
jif,

As you can see, the DIF field is not accessible in your TABLE request.

So, you have to create a HOLD file to be able to test the DIF field. Here is a possibility, using the CAR file.
  
SET ASNAMES=ON
TABLE FILE CAR
IF SEATS NE 5
SUM SALES AS 'SEATS='
BY COUNTRY
ACROSS SEATS COMPUTE DIF/I6=C1 - C2;
ON TABLE HOLD
END
JOIN COUNTRY IN HOLD TO COUNTRY IN CAR AS A_
TABLE FILE HOLD
IF DIF NE 0
IF SEATS NE 5
SUM SALES
BY COUNTRY
ACROSS SEATS COMPUTE DIF/I6=C1 - C2;
END


If you do not need to show the ACROSS field then this is also possible:
  
SET ASNAMES=ON
TABLE FILE CAR
IF SEATS NE 5
SUM SALES AS 'SEATS='
BY COUNTRY
ACROSS SEATS COMPUTE DIF/I6=C1 - C2;
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT *
IF DIF NE 0
END

Good luck....


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
quote:
Originally posted by GinnyJakes:
Cribbing from John's code, try this:
DEFINE FILE EMPDATA
SALINCR/D12.2=SALARY * 1.05;
END
TABLE FILE EMPDATA
SUM
     SALINCR SALARY
COMPUTE TESTDIFF/D12.2 = C1 - C2;
BY DEPT
BY DIV
WHERE TOTAL TESTDIFF GE 5000
ON TABLE HOLD
END			 
TABLE FILE HOLD
SUM TESTDIFF
BY DEPT
ACROSS DIV
END


This seems to work fine. Thanks.

I'll also look at your suggestion Danny. Thanks as well!


WF 7.6.4
 
Posts: 21 | Registered: January 27, 2004Reply With QuoteReport This Post
Member
posted Hide Post
OK. After making something work, I have this issue using the technique above, there's one big show stopper.

Basically my master file has a key with a year and 12 months of data. The user selects a month on the screen and the report will show him the 12 months along with the variance between the month selected and the previous one.

Using the technique above, this works fine. This is the way I coded it to make the filter of the variance work (user selected March):

TABLE FILE MYTABLE
SUM
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC

COMPUTE VARIANCE/I9 = C3 - C2;

BY FIELD1
BY YEAR

WHERE YEAR EQ '2008' OR '2009'
WHERE TOTAL VARIANCE GE 100

ON TABLE HOLD
END

TABLE FILE HOLD
SUM
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC

BY FIELD1
ACROSS YEAR
COMPUTE VARIANCE/I9 = C3 - C2;

END

Works great. Except for one thing. If the user selects January, I'm screwed.


WF 7.6.4
 
Posts: 21 | Registered: January 27, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Do you need to calculate against December? You might be able to do something with the LAST function.

Or do you need to do a rolling 12 months? That makes the logic a bit more complex but doable with some Dialogue Manager.

Or you can test for JAN and make the variance zero.


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, 2006Reply With QuoteReport This Post
Member
posted Hide Post
That's right, I would need to calculate against December (of the previous year of course).

On the code above, I calculate my columns using dialog manager.


WF 7.6.4
 
Posts: 21 | Registered: January 27, 2004Reply With QuoteReport This Post
Member
posted Hide Post
Ok, here's how I did it.

-* (WHERE &MONTH = 1 for Jan, 2 for Feb, ...)
-SET &STARTCOL = &MONTH;
-SET &PREVCOL = &STARTCOL - 1;

-SET &COL1 = 'C' | &STARTCOL;
-SET &COL2 = 'C' | &PREVCOL;

TABLE FILE MAZCV
SUM
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
COMPUTE Variance/I9 = IF &MONTH EQ 1 THEN C1 - LAST C12 ELSE &COL1 - &COL2;

BY FIELD1
BY YEAR
WHERE YEAR EQ '2007' OR '2008'
WHERE TOTAL Variance GE 100;
ON TABLE HOLD AS TEMP1
END

-SET &NBRYEARS = &LINES;

-SET &STARTCOL = &MONTH + ( (&NBRYEARS - 1) * 12 );
-SET &PREVCOL = &STARTCOL - 1;
-SET &PREVYEAR = &STARTCOL - 12;

-SET &COL1 = 'C' | &STARTCOL;
-SET &COL2 = 'C' | &PREVCOL;

SET BYDISPLAY = ON
SET NODATA = 0

TABLE FILE TEMP1
SUM
NOV
DEC
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
BY FIELD1
ACROSS YEAR
COMPUTE Variance/I9 = IF &NBRYEARS LE 1 THEN &COL1 ELSE &COL1 - &COL2;
END


WF 7.6.4
 
Posts: 21 | Registered: January 27, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] WHERE clause on a computed field after an across.

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.