Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have tables as follows:
PhoneCalls:
CustomerID, Call_Start, Call_End
Orders:
CustomerID, Order_Time, Order_ID
I want to append a field to Orders which has value 'O' if the Customer made an order during a phone call (ie. Order_Time is between Call_Start and Call_End) and has value 'L' if the Customer made an order within 7 days of a Call_End.
I feel this should be quite straight-forward but I'm not good with IntervalMatch and I cannot get it working.
Is this a good approach, or is there a better one?
Thanks
And if you want to tag also the order done in the 7 days you can change the end of the script like this:
Left Join (Orders)
IntervalMatch(Order_Time, Customer_ID)
LOAD [Call Start],
[Call End],
Customer_ID
Resident PhoneCalls;
Left Join (Orders)
IntervalMatch(Order_Time, Customer_ID)
LOAD [Call Start] as [Call Start7],
[Call End]+7 as [Call End7],
Customer_ID
Resident PhoneCalls;
DROP Table PhoneCalls;
FinalOrders:
LOAD *,
If(Len(Trim([Call Start])) > 0, 'O', if(Len(Trim([Call Start7])) > 0,'L')) as Flag
Resident Orders;
I think you can do this with Interval Match. Do you have a sample where we can show you how it is implemented?
Hi Sunny
Do you mean a sample of data ?
Yes
Are you able to cut & paste this?:
LOAD * INLINE [
Order_Time, Order_ID,Customer_ID
28/01/2014 17:09, 638060,141161
29/12/2014 09:40, 696482,141161
29/12/2014 09:41, 696480,141161
29/12/2014 09:48, 696481,141161
30/07/2015 09:48, 741114,141161
30/07/2015 09:54, 741054,141162
01/09/2015 10:22, 747415,141163
];
LOAD * INLINE [
Customer_ID,Call Start,Call End
111696,01/09/2015 14:48,01/09/2015 14:59
141161,01/09/2015 09:46,01/09/2015 09:54
130752,01/09/2015 16:30,01/09/2015 16:35
132845,01/09/2015 16:30,01/09/2015 16:35
133837,01/09/2015 10:23,01/09/2015 10:27
133837,01/09/2015 10:32,01/09/2015 10:37
141161,01/09/2015 10:14,01/09/2015 10:24
189125,01/09/2015 14:28,01/09/2015 14:29
189125,01/09/2015 14:56,01/09/2015 14:56
189125,01/09/2015 16:28,01/09/2015 16:30
189126,01/09/2015 14:28,01/09/2015 14:29
189126,01/09/2015 14:56,01/09/2015 14:56
189126,01/09/2015 16:28,01/09/2015 16:30
141162,01/09/2015 14:28,01/09/2015 14:29
189273,01/09/2015 14:56,01/09/2015 14:56
189273,01/09/2015 16:28,01/09/2015 16:30
]
Sure, this works
Hi,
Try this
HistoOrder:
IntervalMatch(Order_Date,customer_Id)
load
Call start
Call end
customer_Id
Resident your secondtable;
Join load *
Resident your secondtable;
May be this:
Orders:
LOAD * INLINE [
Order_Time, Order_ID,Customer_ID
28/01/2014 17:09, 638060,141161
29/12/2014 09:40, 696482,141161
29/12/2014 09:41, 696480,141161
29/12/2014 09:48, 696481,141161
30/07/2015 09:48, 741114,141161
30/07/2015 09:54, 741054,141162
01/09/2015 10:22, 747415,141163
];
PhoneCalls:
LOAD * INLINE [
Customer_ID,Call Start,Call End
111696,01/09/2015 14:48,01/09/2015 14:59
141161,01/09/2015 09:46,01/09/2015 09:54
130752,01/09/2015 16:30,01/09/2015 16:35
132845,01/09/2015 16:30,01/09/2015 16:35
133837,01/09/2015 10:23,01/09/2015 10:27
133837,01/09/2015 10:32,01/09/2015 10:37
141163,01/09/2015 10:14,01/09/2015 10:24
189125,01/09/2015 14:28,01/09/2015 14:29
189125,01/09/2015 14:56,01/09/2015 14:56
189125,01/09/2015 16:28,01/09/2015 16:30
189126,01/09/2015 14:28,01/09/2015 14:29
189126,01/09/2015 14:56,01/09/2015 14:56
189126,01/09/2015 16:28,01/09/2015 16:30
141162,01/09/2015 14:28,01/09/2015 14:29
189273,01/09/2015 14:56,01/09/2015 14:56
189273,01/09/2015 16:28,01/09/2015 16:30
];
Left Join (Orders)
IntervalMatch(Order_Time, Customer_ID)
LOAD [Call Start],
[Call End],
Customer_ID
Resident PhoneCalls;
DROP Table PhoneCalls;
FinalOrders:
LOAD *,
If(Len(Trim([Call Start])) > 0, 1, 0) as Flag
Resident Orders;
DROP Table Orders;
And if you want to tag also the order done in the 7 days you can change the end of the script like this:
Left Join (Orders)
IntervalMatch(Order_Time, Customer_ID)
LOAD [Call Start],
[Call End],
Customer_ID
Resident PhoneCalls;
Left Join (Orders)
IntervalMatch(Order_Time, Customer_ID)
LOAD [Call Start] as [Call Start7],
[Call End]+7 as [Call End7],
Customer_ID
Resident PhoneCalls;
DROP Table PhoneCalls;
FinalOrders:
LOAD *,
If(Len(Trim([Call Start])) > 0, 'O', if(Len(Trim([Call Start7])) > 0,'L')) as Flag
Resident Orders;
Thanks to everyone.
I couldn't get this to work in my model ......and then:
I realised it was because my TimeStampFormat was incompatible ! When I changed it, it was fine. An easy mistake ?