Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Define a COUNT field
Go
New
Search
Notify
Tools
Reply
  
[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, 2017Reply With QuoteReport 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: 1654 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport 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: 3052 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport 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, 2017Reply With QuoteReport 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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport 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, 2017Reply With QuoteReport 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: 1749 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport 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: 1654 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport 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: 864 | Registered: May 24, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Reset it programagically :
...
WHERE FIELD NE LAST FIELD THEN 1 ELSE CNTR + 1 ;
...
???
 
Posts: 3052 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport 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: 864 | Registered: May 24, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Define a COUNT field

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.