Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thank You verry much for this solution and sorry for the late response!
It works well