Hello,

I'm trying to filter a table by the subtotal of a column and having a little trouble.

What I want to do is something like:

 

TABLE FILE
SUM PAID_AMOUNT
BY MEMBER
BY COUNTRY
WHERE MEMBER SUBTOTAL GT 50000
END

 

I know the syntax above isn't correct. I have tried having two SUM statements in the same table file but that takes a while to run and the data isn't accurate when aggregating. I'm hoping there is a way to get the MEMBER SUBTOTAL into another column, like as a BY or a SUM and then I can filter on that static value. Can someone think of another way to do this?

Thank you in advance!

This message has been edited. Last edited by: Brandon Andrathy,

Not sure if this is what you're looking for but WHERE TOTAL might solve this problem.

  
TABLE FILE EMPLOYEE
SUM CURR_SAL
BY LAST_NAME AND BY FIRST_NAME
WHERE TOTAL CURR_SAL EXCEEDS 20000
WHERE DEPARTMENT IS 'MIS'
END

Hey Babak. Thank you very much for the reply.

WHERE Total would work but I would want to filter only by CURR_SAL and LAST_NAME. Not for FIRST NAME as well. Can you think of a way to do that?

As a precursor, Doing something like the below unfortunately takes a very long time to load so I'm looking for a more efficient solution:

 

TABLE FILE EMPLOYEE
SUM 
    CURR_SAL
BY LAST_NAME 
WHERE TOTAL CURR_SAL GT 20000
SUM
    CURR_SAL
BY LAST_NAME
BY FIRST_NAME
END

 

What about this ?

TABLE FILE XYZ
SUM PAID_AMOUNT NOPRINT
BY MEMBER
WHERE TOTAL PAID_AMOUNT GT 50000;
ON TABLE HOLD AS MBRLST FORMAT ALPHA
END
-RUN

TABLE FILE XYZ
SUM PAID_AMOUNT
BY MEMBER
BY COUNTRY
WHERE MEMBER IN FILE MBRLST;
END
-RUN

Hey Martin, thank you!

I actually tried the IN FILE solution already but when there are too many records going into the second hold file, the report crashes. Do you have an idea how to handle more than say 3k records with IN FILE?

I have thought about wanting to JOIN up to the synyonym directly in a table file but apparently that's not feasible lol.

Is this what you are looking for?

TABLE FILE GGSALES
SUM DOLLARS/D12M
    DOLLARS WITHIN PRODUCT NOPRINT
BY  PRODUCT RECOMPUTE
BY  REGION
WHERE TOTAL DOLLARS GT 5000000;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/flat.sty, $
ENDSTYLE
END
  

quote:

Originally posted by Brandon Andrathy:
Do you have an idea how to handle more than say 3k records with IN FILE?

According to this, you shouldn't have any problem using IN FILE. The only point is that the data cannot be a variable field lenght AnV, it must be a fixed field length An :
What is the size limit of a WHERE clause in WebFOCUS 8

Hey Martin, thank you very much for your reply. So it looks like the maximum of 32000 characters applies here. when I look at the WHERE IN FILE condition being utilized in query, it does this:

WHERE MEMBER_ID IN '99999999999', '99999999999'

So as you can see, with the length of my character string, this gets out of control pretty quickly and causes a reporting server error.

From where does your MEMBER_ID for the WHERE clause IN FILE are coming from ?
Do you build the file dynamically ?

Not the most efficient, but an option, if you don't have any other then using a IN FILE (and still limited) clause may be the following:

DEFINE FILE XYZ
ROW_ID /I6 = ROW_ID + 1;
END
TABLE FILE XYZ
SUM PAID_AMOUNT NOPRINT
BY ROW_ID
BY MEMBER_ID
WHERE TOTAL PAID_AMOUNT GT 50000;
ON TABLE HOLD AS EXTMBR
END
-RUN

TABLE FILE EXTMBR
BY MEMBER_ID
WHERE ROW_ID LE 30000;
ON TABLE HOLD AS MBRLST1 FORMAT ALPHA
END
-RUN

TABLE FILE EXTMBR
BY MEMBER_ID
WHERE ROW_ID GT 30000 AND ROW_ID LT 60000;
ON TABLE HOLD AS MBRLST2 FORMAT ALPHA
END
-RUN

TABLE FILE EXTMBR
BY MEMBER_ID
WHERE ROW_ID GT 60000;
ON TABLE HOLD AS MBRLST3 FORMAT ALPHA
END
-RUN

TABLE FILE XYZ
SUM PAID_AMOUNT
BY MEMBER
BY COUNTRY
WHERE MEMBER_ID IN FILE MBRLST1 OR MEMBER_ID IN FILE MBRLST2 OR MEMBER_ID IN FILE MBRLST3;
END
-RUN

Hallway!!!! You just made my day . Wow!! I can't believe I didn't see your post earlier.

Thank you so much!!!!

Martin, thank you also for your suggestion. I was thinking about something like that but didn't know how to go about coding it. Hallway saved the day.

Thanks everyone! I'll go ahead and close.

Excellent!! I'm glad it worked for you.

quote:

Originally posted by Brandon Andrathy:
So it looks like the maximum of 32000 characters applies here.

I use it often.
I have a file where this week has 39 639 records, each with 15 characters long without any issues in a WHERE abc IN FILE xyz.
Your problem was probably somewhere else.

But glad to see that you solved it

@Hallway - +1 Excellent example of using the WITHIN and WHERE TOTAL phrases. Thanks!