Customers with "3rd order..."

[#1]

Hello community.

I have interesting task, in my head I have idea how to solve this, but I not sure that I good enough to put this idea to "script code"...

So please suggest something...

I need to create a table which have inside customers who have 3rd order in specific month (accordingly to order date)  of specific customer.

I have raw source data like:

Order number	Order date (DDMMYYYY)	Customer ID
abc1234	12042016	CustA
sdf2135	10022015	CustB
asw3455	20012013	CustA
ert5432	13042016	CustC
dfg5678	17052016	CustD
asd3456	14052016	CustC
ghj4567	17072016	CustC
sdf4567	30012015	CustA
ewr4325	05072016	CustA
sdf4345	23052015	CustB
etc.......	etc....	etc....

Close to 1 million lines.

As result  I need to have aggregated table, where only customers who have 3 and more orders included and where is date of 3rd order specified:

CustomerID	3rd Order Date
CustA	12042016
CustC	17072016
etc...	...

CustB have only 2 orders, CustC have only 1 order so they out of selection...

I prefer to do this table during script load, because after I need to do some extra actions with this resulting table...

As I understand I should sort source table by customer name then sort orders by order date inside every specific customer, then apply autonumber for orders (but autonumber must be applied for orders inside every specific customer, and for every next customer autonumber of orders must start from 1 again). And finally I will simply select all orders with AutoNumberField = 3.

But I almost have no experience with Autonumber function... Please suggest how to realize such transformation...

Maybe other ideas for transformation logic...

Thank you in advance!

[jpenuliar · Accepted solution]

Hi Maxim,

Maybe below script using FirstSortedValue function is you need:

Temp:

LOAD RecNo(),

     [Order number],

     [Order date],

     [Customer ID]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

FirstSortedValue:

LOAD

    [Customer ID],

    FirstSortedValue([Order number], [Order date],3) as [3rd ORDER NUMBER],

    FirstSortedValue([Order date], [Order date],3) as [3rd ORDER DATE]    

Resident Temp Group By [Customer ID];

[jpenuliar]

here's how it looks like on tables

[#4]

Hello Jonathan,

looks great! I just never used before the function.

will study it.

Thnak you very much!

[vinieme12]

That or you could extend the flexibility within the same table with a flag to show ordernumber

, this way you could do 3rd,4th or whatever number you feel

TEMPTABLE:

LOAD Customer,

     Dateval,

     Month(Dateval) as TranMonth,

  1 as tempval

FROM

(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE

Table1:

LOAD

  Customer,

  Dateval,

  TranMonth,

    if( Customer = Previous(Customer) and TranMonth = Previous(TranMonth),

  rangesum(tempval,peek(OrderNum)),tempval) as OrderNum

RESIDENT TEMPTABLE

Where len(Customer)>0

ORDER BY Customer,Dateval ASC;

Drop Table TEMPTABLE;

[#6]

Hello Vineeth,

also nice solution.

Thank you!