

Go  New  Search  Notify  Tools  Reply 
Member 
I'll explain with this example. Here's sample data along with a simple table file: KEY1 YEAR NUM1 NUM2 A 2007 5 10 A 2008 2 10 B 2007 5 10 B 2008 2 3 TABLE FILE MYTABLE SUM NUM1 NUM2 COMPUTE DIFF/I2 = NUM2  NUM1; BY KEY1 BY YEAR END Simple enough..here's the results: KEY1 YEAR NUM1 NUM2 DIFF A 2007 5 10 5 A 2008 2 10 8 B 2007 5 10 5 B 2008 2 3 1 Now the hard part. I want to put a WHERE clause on the computed field. For example, where the difference is greater than 4 (WHERE TOTAL DIFF GT 4). BUT, I want the condition to apply to 2008 only. If the condition is not met, I don't want the KEY to show up for any year. Basically, if the difference is equal or less than 4 for 2008, I don't want 2007 or 2008 to show up. If the condition is met, I want both years. If I apply that logic to the data above, the result would be: KEY1 YEAR NUM1 NUM2 DIFF A 2007 5 10 5 A 2008 2 10 8 Because KEY1 B for year 2008 had a difference of 1, I didn't want that key to show up in my results. I have no idea how I can do this.This message has been edited. Last edited by: Kerry, WF 7.6.4  

Virtuoso 
Hello jif26, There is probably a shorter and easier approach to what you need but how about this: SET HOLDLIST = PRINTONLY SET ASNAMES = ON * * Create sample data > HMYDATA DEFINE FILE CAR REC_CNT/I2 WITH CAR = REC_CNT + 1; KEY1/A1 WITH CAR= IF REC_CNT FROM 1 TO 2 THEN 'A' ELSE 'B'; YEAR/I4 WITH CAR= IF REC_CNT EQ 1 OR 3 THEN 2007 ELSE 2008; NUM1/I4 WITH CAR= IF REC_CNT EQ 1 OR 3 THEN 5 ELSE 2; NUM2/I4 WITH CAR= IF REC_CNT EQ 4 THEN 3 ELSE 10; END TABLE FILE CAR PRINT REC_CNT KEY1 YEAR NUM1 NUM2 WHERE REC_CNT LE 4 ON TABLE HOLD AS HMYDATA END * * Determine differences by KEY for 2008 TABLE FILE HMYDATA SUM COMPUTE BASE_KEY/A1 = MAX.KEY1; COMPUTE BASE_DIFF/I4 = NUM2  NUM1; BY KEY1 NOPRINT BY YEAR AS 'BASE_YEAR' WHERE YEAR EQ 2008 ON TABLE HOLD AS HGOODKEY FORMAT FOCUS INDEX BASE_KEY END * * Print detail records whose KEY has a difference greater than 4 JOIN CLEAR * JOIN KEY1 IN HMYDATA TO ALL BASE_KEY IN HGOODKEY AS J0 END * TABLE FILE HMYDATA PRINT KEY1 YEAR NUM1 NUM2 COMPUTE DIFF/I4 = NUM2  NUM1; WHERE BASE_DIFF GT 4 END I'm basically calculating the differences for the year 2008 by each key (A and B) joining this results later the source data to get only the keys that match the condition. Cheers,  Neftali. Prod/Dev: WF Server 8008/Win 2008  WF Client 8008/Win 2008  Dev. Studio: 8008/Windows 7  DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.  

Member 
Couldn't I do something more simple by holding data for 2007 and another hold for 2008 (where diff > 4) and then concatenate both hold files but only take 2007 where a 2008 row exists ? TABLE FILE MYTABLE SUM NUM1 NUM2 COMPUTE DIFF/I2 = NUM2  NUM1; BY KEY1 BY YEAR ON TABLE HOLD1 END TABLE FILE HOLD1 SUM NUM1 NUM2 BY KEY1 BY YEAR WHERE YEAR EQ 2007 ON TABLE HOLD AS OLDYEAR END TABLE FILE HOLD1 SUM NUM1 NUM2 BY KEY1 BY YEAR WHERE YEAR EQ 2008 WHERE DIFF GT 4 ON TABLE HOLD AS CURRYEAR END Then concatenate years from CURRYEAR to OLDYEAR only where KEY1 exists on CURRYEAR ? Would a MATCH or MORE phrase work here ? WF 7.6.4  

Expert 
I think Neftali was on the right track but here is a simpler version. TABLE FILE YRNUM SUM COMPUTE DIFF1/I4=NUM2NUM1; BY KEY1 WHERE YEAR EQ 2008 WHERE TOTAL DIFF1 GT 4 ON TABLE HOLD AS DIFF1 FORMAT ALPHA END JOIN KEY1 IN DIFF1 TO ALL KEY1 IN YRNUM AS J1 TABLE FILE DIFF1 PRINT NUM1 NUM2 BY KEY1 BY YEAR END If you put the keys in one file where the diff is greater than 4 for 2008, then you can use that file as the host of a join. If you do an inner join, you will get all of the records for a KEY1 where the diff is greater but you will not get the records if it is not. Let us know what you think. Ginny  Prod: WF 7.7.01 Dev: WF 7.6.911 Admin, MRE,selfservice; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google  

Virtuoso 
How about something like this: TABLE FILE MYTABLE SUM NUM1 NUM2 COMPUTE DIFF/I2 = IF YEAR EQ 2008 THEN (NUM2  NUM1) ELSE LAST DIFF ; BY KEY1 BY HIGHEST YEAR WHERE TOTAL DIFF GT 4 ; END WebFOCUS 7.7.05  

Member 
Thanks. I guess I had sort of the same idea. This is what I did before I saw your post: TABLE FILE MYTABLE SUM NUM1 NUM2 COMPUTE DIFF/I2 = NUM2  NUM1; BY KEY1 BY YEAR ON TABLE HOLD1 END TABLE FILE HOLD1 BY KEY1 WHERE YEAR EQ 2008 WHERE DIFF GT 4 ON TABLE HOLD AS HOLD2 END MATCH FILE HOLD1 SUM NUM1 NUM2 DIFF BY KEY1 BY YEAR RUN FILE HOLD2 BY KEY1 AFTER MATCH HOLD OLDANDNEW END WF 7.6.4  

Expert 
Well, mine is shorter. And you have a syntax error on your first hold statement. But other than that ,it works just fine. A caveat would be that MATCH won't always work for a one to many whereas JOIN always will. Ginny  Prod: WF 7.7.01 Dev: WF 7.6.911 Admin, MRE,selfservice; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google  

Powered by Social Strata 
Please Wait. Your request is being processed... 