I have an excel spreadsheet with all the orders lines (so 1 order can spread on multiple lines).
My goal is to be able to tag the first orders for each client ==> From my table I can now get the customer data with the first order date and the last order date. Therefore if the order_date = customer_first_order_date then I can flag the order as a "first order".
Could you help me doing that?
As of now, here is the script I have :
DATE(FLOOR(FIRST_INVOICE_DATE)) as OrderDate,
If(LINE_TYPE='Product',QUANTITY,0) as ProductRecordCount,
(ooxml, embedded labels, table is [InvoiceDetail]);
date(min(OrderDate)) as invoice_date,
1 AS OrderRecordCount,
sum(Line_amount) as CA_HT_NET,
count(OrderID) as OrderLinesCount
group by OrderID;
date(min(OrderDate)) as first_order_date,
date(max(OrderDate)) as last_order_date,
count(distinct(OrderID)) as nborders
group by CONTACT_EMAIL;
==> At this step, I would like to add the field IsFirstOrder in the OrderData table (but may be this is not the right way to do that, feel free to propose me your solution ;-))
May be I was not clear enough... In fact, I just would like to be able to tag first order at load time if possible. For that, my starting point is the list of all the orders lines (so 1 order can spread on multiple lines).
I was counting on the fact that first_order_date was created as max(OrderDate), so it seems it should work. Do you have a range of dates for each CONTACT_EMAIL. Can you post a sample QVW or sample data?
Attached are an excel spreadsheet with some examples :
Example : first order for f.andrieux is 08/07/2011. Therefore, the order CMNF1100000704 is tagged FirstOrder=1 while the orders CMNF1100000750 & CMNF1100000853 (from the same customer) are tagged FirstOrder=0.