Calculating Range from Visit to Order

[#1]

Hi,

I would like to get following calculated informations out of the table below:

Visits till first order (4 visits)

Time since first visit till first order ( 3days 1hour) 13.01.01 13:18:00 - 10.01.01 12:12:00

Visits since last order till next order (3 visits)

Time since first visit after last order till next order (2days 6min 1sec)  18.01.01 13:19:11 - 16.01.01 13:13:10 

This should go on in a loop.

If after the last visit no order-timestamp exists the script should count till today().

timestamp= dd.mm.yy hh:mm:ss

customer:

johndoe

visits(timestamp)

10.01.01 12:12:00

11.01.01 12:12:11

12.01.01 12:12:12

13.01.01 12:12:13

16.01.01 13:13:10

17.01.01 13:13:12

18.01.01 13:13:11

19.01. 12:00:00

orders(timestamp)

13.01.01 13:18:00

18.01.01 13:19:11

[swuehl · Accepted solution]

Maybe like attached? Using this script:

TS:

LOAD TS, 'Orders' as Type INLINE [

TS

13.01.01 13:18:00

18.01.01 13:19:11

];

Let vNumOrders = NoOfRows('TS');

LOAD TS, 'Visits' as Type INLINE [

TS

10.01.01 12:12:00

11.01.01 12:12:11

12.01.01 12:12:12

13.01.01 12:12:13

16.01.01 13:13:10

17.01.01 13:13:12

18.01.01 13:13:11

19.01.01 12:00:00

];

TMP:

LOAD *, if(Type= 'Visits', AutoNumber(TS, OrderNum) ) as VisitNum;

LOAD TS, Type, if(recno() = 1,$(vNumOrders)+1, peek(OrderNum) - if(Type='Orders',1,0)) as OrderNum

Resident TS order by TS desc;

drop table TS;

Result:

LOAD OrderNum,

          max(VisitNum) as DeltaVisits,

          interval(if(LastValue(Type) <> 'Orders', now(), LastValue(TS)) - FirstValue(TS),'dd  hh:mm:ss' )   as DeltaTime ,

          if(LastValue(Type) <> 'Orders', true() , false() ) as OrderPending

Resident TMP group by OrderNum order by TS asc;

[#3]

Thank You verry much for this solution and sorry for the late response!

It works well