 | |
#21
 
| |||
| |||
|
Re: Oreder before a certain date -
06-28-2011
, 03:27 PM
|
I'm surprised nobody remembered to use REFORMAT. |
was being used. Not all versions support this verb. However, I agree
that REFORMAT / SREFORMAT is an often overlooked Pick verb.
Quote:
|
1. CREATE-FILE TEMP.CUSTOMERS |
Tony G., which uses list manipulation. This avoids having to create
any extra, temporary files.
Quote:
|
2. REFORMAT ORDERS WITH ORDER.DATE LE "06/28/08" CUSTOMER.ID This effectively creates a record in the secondary file, whose ID is CUSTOMER.ID |
expect. What you've posted will create records in the secondary file
with an ID the same as the order ID, and attribute 1 will contain the
CUSTOMER.ID. To get items with an ID the same as the CUSTOMER.ID, you
need to add ID-SUPP or (I) to the statement.
There is a possible downside to this approach too. Since the result is
strictly based on the data in the ORDERS file, it will not include any
customers that have never placed an order (e.g. new customers). Some
would say that if they've never placed an order, they are not a
customer, but that depends entirely on the business and what an order
represents to that business.
--
Kevin Powick
#22
| |||
| |||
|
|
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? |
isn't getting to the group.
My suggestion, since we do similar reporting for customers and
products, is to add an attribute to the customer file, named
"LAST.ORDER.DATE", or something like that. Then, write a quick
program that goes through your order file, gathers the "ORDER.DATE",
and writes it to the customer file - ONLY IF THE DATE ON THE ORDER IS
MORE RECENT than the last date posted to LAST.ORDER.DATE. When you
are finished with that process, you caan simply "SELECT CUSTOMER WITH
LAST.ORDER.DATE < .....".
Lastly, add the code to your order program, posting DATE() to
LAST.ORDER.DATE. You will then be set for various selections
against your customer file for "LAST.ORDER.DATE".
We post order-date to our product file, so as to know when it was last
ordered for replenishment. Same hold true for web-sale-based
customers, where "order" date, and "ship" date are both captured, by
order, by customer.
Good luck.
Jim Cronin
Kittery Trading Post
#23
| |||
| |||
|
|
This is the third attempt for posting this message - don't know why it isn't getting to the group. |
are missing from the Google's usenet feed for CDP.
I use a usenet feed, not google to follow CDP.
--
Kevin Powick
#24
| |||
| |||
|
|
On 2011-06-28 14:40:51 -0400, Matt <mstern (AT) comprehensive (DOT) com> said: I'm surprised nobody remembered to use REFORMAT. Until today, we did not know which version of Pick (revealed as D3/NT) was being used. Not all versions support this verb. However, I agree that REFORMAT / SREFORMAT is an often overlooked Pick verb. 1. CREATE-FILE TEMP.CUSTOMERS Now knowing the Pick version, I would opt for a solution as posted by Tony G., which uses list manipulation. This avoids having to create any extra, temporary files. 2. REFORMAT ORDERS WITH ORDER.DATE LE "06/28/08" CUSTOMER.ID This effectively creates a record in the secondary file, whose ID is CUSTOMER.ID Actually, I believe that syntax will not produce the results you expect. What you've posted will create records in the secondary file with an ID the same as the order ID, and attribute 1 will contain the CUSTOMER.ID. To get items with an ID the same as the CUSTOMER.ID, you need to add ID-SUPP or (I) to the statement. There is a possible downside to this approach too. Since the result is strictly based on the data in the ORDERS file, it will not include any customers that have never placed an order (e.g. new customers). Some would say that if they've never placed an order, they are not a customer, but that depends entirely on the business and what an order represents to that business. -- Kevin Powick |
--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---
#25
| |||
| |||
|
|
On 2011-06-29 11:29:18 -0400, JJCSR <JCronin (AT) ktp (DOT) com> said: |
Quote:
|
This is the third attempt for posting this message - don't know why it isn't getting to the group. I've seen all of your messages come through, but I noticed that posts are missing from the Google's usenet feed for CDP. I use a usenet feed, not google to follow CDP. -- Kevin Powick |
--- Posted via news://freenews.netfront.net/ - Complaints to news (AT) netfront (DOT) net ---
#26
| |||
| |||
|
|
On 2011-06-27 08:23:49 -0400, Rich Taylor <richtaylor0... (AT) gmail (DOT) com> said: The solution proposed by Kevin seems best. Well, I'm not sure about "best", but I intended it to cater to the lowest common denominator of "Pick" type systems. *It "should" work for almost any MV system that's still running today. Without making system modifications or creating new files, list manipulation, as shown in TG's solution, is a better approach. *He's shown how it can be done in D3, and I know that OpenQM has similar TCL verbs to do the same. *I'm not sure about other platforms. -- Kevin Powick |
trouble with my replys to CDP at my work site.
What I would propose is something that may be more productive in the
long run. I am suggesting to add an attribute to the "customer"
file, call it LAST.ORDER.DATE. Write a simple program that reads
through the entire "order" file, collecting the last order date from
items in the order file. As you read an order for a customer that
has an order date that is more recent then the date already posted to
the customer belonging to the order, write that date in the new
attribute. When all orders are processed in this manner, you can
then select against the customer file (e.g. SELECT CUSTOMER WITH
ORDER.DATE < "06/29/08" AND NE ""). Make certain you include the
'AND NE "" ', as you don't want to include customers that are new,
without orders placed.
Now, modify the programs(s) that relate to placing the orders, as you
want to start capturing order-date for all orders. The new attribute
in customer (LAST.ORDER.DATE) will now be up-to-date for any similar,
subsequent inquirys. If you found a reason to make the inquiry that
started this thread, you will more than likely have additional
inquiries like this one.
Good luck,
Jim Cronin
Kittery Trading Post.
#27
| |||
| |||
|
|
I dunno if non-D3 flavors have REFORMAT, or if is moot point 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 |
SMA. I want to say it was a product of the minds at ADDS but I can't
quite be sure on that. At any rate it exists on Universe, MvBase and
MvEnterprise as well as D3. I can't speak for Unidata, but I suspect
it.
Will Johnson
#28
| |||
| |||
|
|
Yes Frank, REFORMAT has existed on all pick versions since the days of SMA. I want to say it was a product of the minds at ADDS but I can't quite be sure on that. |
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.