Hi there,

I am a new forum user and this is my very first post, so, in the first place, hail to everybody.

I am trying to export to a txt file the order placed by every customer in the past year, grouped by months. I succeeded in grouping and formatting the data as I need them, but still have a problem I am not able to overcome. It happens that the majority of the customers did not place at least an order every month. So, the exported data are as follows:

Monster Inc. 8 10000 10 5000 11 7000

Big Company Ltd. 5 20000 9 3000

Very Big Company Inc. 3 100000 4 150000 8 60000

The first "column" is the customer name, then a variable number of "couples" (month number and value of orders in euros) follows, so - for instance - Monster Inc. in the past year placed orders for a total of 10 thousands euro in August, 5 thousand in October 7 thousands in November and so forth.

What I need instead is to have for every customer exactly 12 "couples" month/orders with 0 as value for months without orders:

Monster Inc. 1 0 2 0 3 0 4 0 5 0 6 0 7 0 8 10000 9 0 10 5000 11 7000 12 0

How could I do that?

Thanks in advance,

George

If this is DataEase 7 there is fewer alternatives than in 8 but still possible ;-)

I will concentrate on the brute force and you can do the details of exporting it yourself.

define temp "january" number .
define temp "februay" number .
define temp "march" number .

etc..
for MyCustomers ;
january := sum of MyOrders with month(invoicedate) := 1 OrderTotals .
february := sum of MyOrders with month(invoicedate) := 2 OrderTotals .

march := sum of MyOrders with month(invoicedate) := 4 OrderTotals .

etc.

list records
CustomerName ;
January ;
Februar ;
March ;

If you want to do it for a particular year just add and year(invoicedate) := Data-entry Year or something like that.

In DataEase 8 you could have done this in ExecDQL and then you could have simply added a export file name and it would have been a done deal. In DataEase 7 you will need to do an export definition at the end of the DQL.

Hi Mr. DataEase and thank you for answering.

I tried a modified version of your solution (I have added an additional selection criterion on the customer: without that it gives me the same total for each customer) and it has some problems. The first one is that dataease can't manage all those variables: if I put 12 variables it crashes, if I put just 3 it works. The second one is that is slow

My approach was like this:

for Orders with ( flag = 0 ) ; --The flag is set somewhere else in the application
list records
cod_customer in groups named "grpCust" ;
month ( InvoiceDate ) in groups named "grpInvoice" ; --Here's the issue: I have "records" just for months in which at least one order is placed
OrderTotalValue : item sum .
end

This is very fast, but the problem is that I need "grpInvoice" also for months in which the customer did not place orders, so I am wondering if there is a way to add those "dummy" records using my structure.

Regards.

Hi Again.

The alternative is to create a nested DQL where you run a For inside a for, and then the second one is nesting a table where you have only one record for each month.

for MyData ;
for MyMonths ;
list records
Sum of MySales with Month(salesdate) = MyMonth Month Sales .
MyData Customername .
end
end.

This is very simplistic, but what you do is to run through all 12 months for each Customer and see if there is any sales for that month. If there is, then it will be larger than 0 if not it will result in 0.

In MyMonth you simply have one record saved for each month.

That's smart, a sort of join with another table, thank you now it works.