IntervalMatch or another solution?

[richard_chilvers]

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

[sunny_talwar]

I think you can do this with Interval Match. Do you have a sample where we can show you how it is implemented?

[richard_chilvers]

Hi Sunny

Do you mean a sample of data ?

[sunny_talwar]

Yes

[richard_chilvers]

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

]

[sunny_talwar]

Sure, this works

[syukyo_zhu]

Hi,

Try this

HistoOrder:

IntervalMatch(Order_Date,customer_Id)

load

  Call start

  Call end

  customer_Id

Resident your secondtable;

Join load *

Resident your secondtable;

[sunny_talwar]

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;

[vincent_ardiet · Accepted solution]

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;

[richard_chilvers]

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 ?