Focal Point
[CLOSED] Define a COUNT field

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6157046886

December 14, 2017, 10:57 AM
jkripal
[CLOSED] Define a COUNT field
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
December 14, 2017, 11:32 AM
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.


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 :
December 14, 2017, 11:54 AM
Doug
Good one Wep, Or, change the EQ to GT and you'll get the returning customers. Just thinking out loud...
December 14, 2017, 12:07 PM
jkripal
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
December 18, 2017, 10:58 AM
GamP
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
December 18, 2017, 11:48 AM
jkripal
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
December 18, 2017, 11:51 AM
FP Mod Chuck
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
December 19, 2017, 05:57 AM
Wep5622
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 :
December 19, 2017, 09:37 AM
TexasStingray
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

Reset it programagically :
...
WHERE FIELD NE LAST FIELD THEN 1 ELSE CNTR + 1 ;
...
???
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