[SOLVED] WHERE clause on a computed field after an across.
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 expectedThis message has been edited. Last edited by: Kerry,
WF 7.6.4
December 02, 2009, 01:39 PM
JohnB
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
December 02, 2009, 01:56 PM
jif26
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
December 02, 2009, 02:28 PM
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
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
December 02, 2009, 02:43 PM
jif26
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
December 03, 2009, 09:10 AM
jif26
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
December 03, 2009, 09:25 AM
GinnyJakes
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.
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