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     [CLOSED] Define a COUNT field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Define a COUNT field
 Login/Join
 
Member
posted
I'm trying to define a count field and am stuck on how to do this...

Every time a customer purchases something on our platform, a new record is created in this table which includes the CustomerID. I want to get a define in place that counts if this CustomerID has appeared previously in the table so I can know if this is a new or returning customer via a filter on the count. The SQL equivalent of this I have is this...

select CustomerID, count(*) from CustomerRegistration group by CustomerID having count(*) = 1

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: December 13, 2017Report This Post
Virtuoso
posted Hide Post
TABLE FILE CustomerRegistration
SUM COMPUTE CNT/I10 = CNT.CustomerId
BY CustomerId
WHERE TOTAL CNT EQ 1;
END


Is there no other field to distinguish multiple Registration records for the same customer? You seem to not actually be interested in the counts, but rather in the non-existence of other records for the same customer.

Something like:
select CustomerId
  from CustomerRegistration R1
 where not exists (
   select 1
     from CustomerRegistration R2
    where R2.Customerid = R1.CustomerId and R2.RegistrationDate <> R1.RegistrationDate
)


The principle is called a "correlated subquery". It is usually a faster query than one using COUNT, as COUNT has to visit every record per customer, while NOT EXISTS can stop looking as soon as a 2nd record is encountered.

WebFOCUS has an equivalent for that in the DB_INFILE function, where EXISTS can be tested using WHERE DB_INFILE(...) EQ 1; and NOT EXISTS using WHERE DB_INFILE(...) EQ 0;.
Leaving the equality comparison out translates to WHERE EXISTS too.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Expert
posted Hide Post
Good one Wep, Or, change the EQ to GT and you'll get the returning customers. Just thinking out loud...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Member
posted Hide Post
quote:
Originally posted by Wep5622:
TABLE FILE CustomerRegistration
SUM COMPUTE CNT/I10 = CNT.CustomerId
BY CustomerId
WHERE TOTAL CNT EQ 1;
END


Is there no other field to distinguish multiple Registration records for the same customer? You seem to not actually be interested in the counts, but rather in the non-existence of other records for the same customer.

Something like:
select CustomerId
  from CustomerRegistration R1
 where not exists (
   select 1
     from CustomerRegistration R2
    where R2.Customerid = R1.CustomerId and R2.RegistrationDate <> R1.RegistrationDate
)


The principle is called a "correlated subquery". It is usually a faster query than one using COUNT, as COUNT has to visit every record per customer, while NOT EXISTS can stop looking as soon as a 2nd record is encountered.

WebFOCUS has an equivalent for that in the DB_INFILE function, where EXISTS can be tested using WHERE DB_INFILE(...) EQ 1; and NOT EXISTS using WHERE DB_INFILE(...) EQ 0;.
Leaving the equality comparison out translates to WHERE EXISTS too.


Where would I put the DB_INFILE piece? You're right, that does seem much more efficient. Sorry, I'm very new (only been usuing WebFOCUS for a few weeks)


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: December 13, 2017Report This Post
Virtuoso
posted Hide Post
You could try to look up the information on the db_infile function using the various help mechanisms available to you.
There is a quite useful help file installed with my App Studio, called function.chm. It contains very complete information on all standard functions included in WebFocus.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Member
posted Hide Post
I should also mention that it has to work with BUE, so therefore you can't code it except in a compute or define. Any suggestions?


WebFOCUS 8
Windows, All Outputs
 
Posts: 4 | Registered: December 13, 2017Report This Post
Virtuoso
posted Hide Post
Hi Jkripal

You can try to put a DEFINE in the master file.

DEFINE CUSTOMER_COUNT/I11 = 1;


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
It has to work with BUE? The British University in Eqypt? (According to Google/Wikipedia)

Well, whatever that means, you can just as easily assign the result of DB_INFILE to a virtual field and test on that in your WHERE filter.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Master
posted Hide Post
If BUE (Business Users Edition) allows you to create and reuse hold files you could just sum the count by the customerid and hold it. Then join the main file to the hold file using left outer that should give you a count and (null or zero) for the customerids that are not in there.




Scott

 
Posts: 865 | Registered: May 24, 2004Report This Post
Expert
posted Hide Post
Reset it programagically :
...
WHERE FIELD NE LAST FIELD THEN 1 ELSE CNTR + 1 ;
...
???
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Master
posted Hide Post
If you are just trying to see if the customer id is in the database table, then you also do it with a define using the DB_LOOKUP function and have it return the customer id.

Then if the customer id is blank or is missing (if the customer id column in the table has nulls on) then you know its a first time else its a returning customer.




Scott

 
Posts: 865 | Registered: May 24, 2004Report 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     [CLOSED] Define a COUNT field

Copyright © 1996-2020 Information Builders