A better script design

[#1]

Hello every body.

I need to read a table with invoice's data , and qualify the customer as "New" or "Old" if he has any invoice during the previous year. "If the customer of the invoice has any invoice related in the previous year (from the invoice date) is an OLD customer, if not, is a new customer"

I've tried to work with the following script, counting the number of invoices for customer, but I thinks that there must be an easy way

Sales1:

LOAD

InvoiceNumber,

InvoiceDate,

Customer

FROM Sales.dbf;

LEFT JOIN

LOAD

Customer,

InvoiceDate as Date2,

InvoiceNumber as InvoiceNumber2

FROM Sales.dbf;

LOAD

InvoiceNumber,

InvoiceDate,

Customer,

count(InvoiceNuber2)

WHERE Date2<=InvoiceDate and Date2>Invoicedate-365

GROUP BY InvoiceNumber, InvoiceDate, Customer;

Can anybody help me?

[Anonymous]

I don't see how it meets the requirement "If the customer of the invoice has any invoice related in the previous year (from the invoice date) is an OLD customer, if not, is a new customer"...
Here is how I understand it from your description:

Sales1:
LOAD
 InvoiceNumber,
 InvoiceDate,
 Customer
FROM Sales.dbf;
JOIN (Sales1) LOAD DISTINCT
 Customer,
 Customer as OldCustomer,
 'Old' as "Old/New"
RESIDENT Sales1
// Only one of the following two "WHERE" lines must be used, depending on the requirements:
WHERE year(InvoiceDate) = year(today())-1; // has invoices in last year
WHERE InvoiceDate < AddMonths(today(), -12) ; // has invoices older than year
JOIN (Sales1) LOAD DISTINCT
 Customer,
 'New' as "Old/New"
WHERE not exists(OldCustomer. Customer); // All customers who are not old
DROP FIELD OldCustomer;

As for the counting, it's probably better to do on the front end in this case.

Regards,
Michael

[#3]

Thank you Michael,

Thats exactly what I was looking for.

Regards
Marcelo