Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Subtotal of column as a By Field or a Compute

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Subtotal of column as a By Field or a Compute
 Login/Join
 
Platinum Member
posted
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,


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
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

 


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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.


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Master
posted Hide Post
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
  


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
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.


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Platinum Member
posted Hide Post
Hallway!!!! You just made my day Smiler Smiler . 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.


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Master
posted Hide Post
Excellent!! I'm glad it worked for you.


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Virtuoso
posted Hide Post
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


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
@Hallway - +1 Excellent example of using the WITHIN and WHERE TOTAL phrases. Thanks!
 
Posts: 822 | Registered: April 23, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Subtotal of column as a By Field or a Compute

Copyright © 1996-2020 Information Builders