Hi everyone, I am not knew to Webfocus but kind of new to this board. I do alot of lurking and have learn SO MUCH from you guys. But, I am stumped on how to create a consecutive counter for a given account activity.
For example I am looking to capture customers who had a consecutive charge to their account for a period of 4 days. The charge has to be 4 days consecutively. If there are two days where they didnt have a charge, but then on the third day they do; I want my counter to start over and now catpure the consecutive days. I know I have to create a define that sets a counter and that the counter has to start over when the pattern breaks, but for the life of me I cannot figure this out.

The data will look like this:

CustID Same ChargeNum Cnter day 1
CustID Same ChargeNum Cnter0 +1 day 2
CustID diff ChargeNum 0 day
CustID Same ChargeNum Cnter day 1
CustID Same ChargeNum Cnter0 +1 day 2
CustID Same ChargeNum Cnter0 +2 day 3
CustID Same ChargeNum Cnter0 +3 day 4

I cannot get my define to do the counter so I can say in my select statement Where conseccnt eq 4.

Define File Counter
Cnt/I5 = If customerid eq last customerid and chargenum eq last charge num then 1 else 0;
end

Can anyone help me with this one.

I understand from your description that the records are already in customerid and Date order. I assume that Date has a smart-date usage (e.g., MDYY), and allow for multiple charges on the same Date. Try this:



DEFINE FILE Counter
Cnt/I5 =
-* first charge for this account? (reset counter)
IF (customerid NE LAST customerid) THEN 1
-* same acount and date?
ELSE IF (Date EQ LAST Date ) THEN Cnt
-* same account, next consecutive date?
ELSE IF (Date EQ LAST Date + 1) THEN Cnt + 1
-* same account, non-consecutive date (reset)
ELSE 1;
END
-* List all transactions of qualifying accounts:
TABLE FILE Counter
SUM MAX.Cnt NOPRINT
BY customerid
PRINT

BY customerid
BY Date
IF TOTAL MAX.Cnt GE 4
END

This message has been edited. Last edited by: <Mabel>,

Yes, they are smartdates. A date is posted each time a charge is made to the account. I think this is EXACTLY what I need. Thanks!