Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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];
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];
here's how it looks like on tables
Hello Jonathan,
looks great! I just never used before the function.
will study it.
Thnak you very much!
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;
Hello Vineeth,
also nice solution.
Thank you!