topic Re: IntervalMatch or another solution? in QlikView

IntervalMatch or another solution?

richard_chilvers — Wed, 18 Nov 2015 13:37:34 GMT

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

Re: IntervalMatch or another solution?

sunny_talwar — Wed, 18 Nov 2015 13:50:34 GMT

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

Re: IntervalMatch or another solution?

richard_chilvers — Wed, 18 Nov 2015 13:54:31 GMT

Hi Sunny

Do you mean a sample of data ?

Re: IntervalMatch or another solution?

sunny_talwar — Wed, 18 Nov 2015 13:59:57 GMT

Yes

Re: IntervalMatch or another solution?

richard_chilvers — Wed, 18 Nov 2015 14:13:41 GMT

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

]

Re: IntervalMatch or another solution?

sunny_talwar — Wed, 18 Nov 2015 14:15:22 GMT

Sure, this works

Re: IntervalMatch or another solution?

syukyo_zhu — Wed, 18 Nov 2015 14:17:11 GMT

Hi,

Try this

HistoOrder:

IntervalMatch(Order_Date,customer_Id)

load

Call start

Call end

customer_Id

Resident your secondtable;

Join load *

Resident your secondtable;

Re: IntervalMatch or another solution?

sunny_talwar — Wed, 18 Nov 2015 14:24:17 GMT

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;

Re: IntervalMatch or another solution?

vincent_ardiet — Wed, 18 Nov 2015 14:44:05 GMT

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;

Re: IntervalMatch or another solution?

richard_chilvers — Wed, 18 Nov 2015 15:00:57 GMT

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 ?