Being a Friday I can't seem to think. My brain has left for the
weekend, so I ask the group:

I have a file with customer orders, how would I get a list of
customers who have not placed an order in the last three years?

You would need to do something like:

SELECT the orders file for orders in the last three years

The objective here is to get a select list of the customer ids who have
ordered. Then:

NSELECT the customers file.

Cheers,

Brian

On 24/06/2011 6:54 p.m., ck wrote:

On 6/24/11 12:52 PM, Brian Speirs wrote:
OP didn't mention which O/S, so NSELECT might not be available.
Here's a "vanilla" eqivalent, written for uniData:
CREATE-FILE TEMP.CUST 1 11
select ORDER.FILE SAVING UNIQUE CUSTOMER.ID
UPDATE TEMP.CUST [ENTER] 1 [ENTER] 1 [ENTER]
SELECT ORDER.FILE WITH ORDER.DATE GE "6/24/2008"
select ORDER.FILE SAVING UNIQUE CUSTOMER.ID
DELETE TEMP.CUST [ENTER] Y [ENTER]
SELECT TEMP.CUST

YMMV, of course, depending on your O/S, the names of the Order and
Customer file, the attribute name in the DICT of the Order file for
the Customer ID.

--
frosty

Since when is that syntax "vanilla"?

Since I've only been writing in uniData for the last 9+ years. =`;^p

Actually, I set out to write a vanilla solution, then realized that I
didn't have a vanilla system to test it on. (And that my vanilla skillz
had evaporated over the last decade.) As Tony points out, this is a
uniData solution. But "YMMV... depending on your O/S" applies.

I suppose you could use "EDIT" instead of "UPDATE" then build a
prestore command that does "FI" on each edited record. But I
hesitate to post
=c fi`=c [ENTER] =c [ENTER]
and that nasty ESC syntax for vanilla Pick is just... nasty.

Perhaps there's an equivalent of uniData UPDATE in teh Pick?

--
frosty

On 2011-06-24 17:26:41 -0400, Tony Gravagno
<tony_gravagno (AT) nospam (DOT) invalid> said:

The very vanilla approach - Assuming goal is to get a list of customers.

- Create a file TEMP-CUSTOMERS
- Copy customers to temp-customers
- Select customer id from order file for orders GE 3yr old date
- Delete items from temp customers
- Select temp-customers (you now have your desired list).

e.g.

CREATE-FILE TEMP-CUSTOMERS 1 17

COPY CUSTOMERS *
TO: (TEMP-CUSTOMERS

SELECT ORDER-FILE CUST.ID WITH ORDER.DATE GE "24JUN08"

DELETE TEMP-CUSTOMERS

SELECT TEMP-CUSTOMERS

A cleaner, less primitive solution would be to write a simple BASIC
program to build a list and then save it.

--
Kevin Powick

I just took a shot at this in D3, not wanting to create a temp file
that included complete records.
(Comments are in parentheses)


(Get all subscribers = customers)
:SELECT REG.SUBSCRIBER

[404] 48 items selected out of 48 items.
(Save the list of everyone)
List 'SUB1' in file 'pointer-file' saved.

(Get recent transactions = orders)
:SELECT REG.SUBSCRIPTION WITH DATE > 05/15

[404] 43 items selected out of 89 items.

(Within those items, extract the subscriber ID)
[404] 43 items selected out of 43 items.
(Save recently referenced subscriber/customer IDs)
List 'SUB2' in file 'pointer-file' saved.
(GL is shortcut for GET-LIST, note Unique option)
:GL SUB2 (U

pointer-file 'SUB3' size = 228

(Re-save the now shorter list, SL=SAVE-LIST)
List 'SUB2' in file 'pointer-file' saved.

(Helpful list operation)
:COMPARE-LIST SUB1 - SUB2 SUB3

(Final list is in SUB3, sort it)
:SORT-LIST SUB3

(Edit-list, now has 31 items)
:EL SUB3

With the above, and other suggestions in this thread, someone can
quickly create a cross-platform verb (or wrapper for QSelect) which
performs this relatively common data operation.

HTH

"Tony Gravagno" wrote
on account of them already having the features you used above
{ QSELECT verb and (U option of GET-LIST }
but you could instead just write items in a TMP file of the qualifying
order dates as mvalues on a1, with item ids in TMP being customer id;

CLEAR-FILE TMP
SELECT ORDERS WITH DATE > "01/01/2008"
REFORMAT ORDERS CUST.ID DATE ID-SUPP
file name : ? TMP

LIST TMP A1
TMP A1
TOM 15800
14904
15530
DICK 14841

customers tom and dick were found in ORDERS in the DATE range requested.
You might want to also preserve associated ORDERS item ids as TMP A2 ;
REFORMAT ORDERS CUST.ID DATE A0 ID-SUPP
file name :? TMP

If you are working on an older system that doesn't do NSELECT then you
probably should have written your own by now Of course I'm
reminded of 1984 and newspeak--if you don't have a word for something,
then you may never have thought about the idea the word represents)

I guess you aren't lucky enough to have a LAST.ORDER.DATE field in the
customer file?

This is easier on some platforms than others. On universe, unidata,
cache, and I think jbase:

SELECT ORDER-FILE WITH EVERY ORDER.DATE < "06/25/2008" SAVING UNIQUE
CUSTOMER

On unidata there are some special functions that you can only use in
itypes: ADD_MONTHS() and MONTHS_BETWEEN() that would obviate hard-
coding the date if you do this often. For non-unidata, some f-
correlative gymnastics might be fun. (sorry unidata users--no f-
correlative gymnastics for you)

The above query only gets you a list of customers who have ever
actually ordered. To get the ones who haven't, hopefully you can:

SELECT CUSTOMER-FILE WITH NO ORDER.POINTERS

or

SELECT CUSTOMER-FILE
NSELECT ORDER-FILE

(if the order-file is keyed by customer)

and then merge the 2 lists with MERGE.LIST. If you are on a system
without MERGE.LIST, then maybe you should have written your own by now
too

D3 doesn't have the SAVING keyword. Again, you must have some utility
that takes a list and removes duplicates.

On 6/25/11 10:56 PM, Ed Clark wrote:
"A flute without holes is not a flute."

And a Customer without Orders is not a Customer.

IMO, of course; YMMV.

--
frosty