I have a table with log of Buyer Activity. We would like to track whether each enquiry get 1st response for each online enquiry <= Date
Eg: buyerid B, There are 2 enquiries on 10 Jan 2023, 9am & 10am, and response on 10 Jan 2023 10.30am will match to both record.
| Date |
buyerid |
Enquire Type |
Type |
| 01-01-2023 9:00am |
A |
Online |
Enquire |
| 01-01-2023 9.00am |
B |
Online |
Enquire |
| 10-01-2023 9.00am |
B |
Offline |
Enquire |
| 10-01-2023 10.00am |
B |
Online |
Enquire |
| 01-01-2023 10.30am |
B |
Offline |
Response - Phone |
| 10-10-2023 10.05am |
B |
Offline |
Response - Phone |
| 11-10-2023 11.00am |
B |
Offline |
Response - Meetup |
| 02-01-2023, 11am |
C |
Online |
Enquire |
| 03-01-2023,12pm |
C |
Online |
Enquire |
| 03-01-2023, 5pm |
C |
Offline |
Response - Phone |
| 03-01-2023, 11am |
D |
Online |
Enquire |
| 03-01-2023,12pm |
D |
Online |
Enquire |
| 03-01-2023, 5pm |
D |
Offline |
Response - Phone |
The logic is: for each online enquiry, we have to find out the response date time. The response date time must be later than the enquire date else, there is no response to the enquiry.
This is the expected result:
| Enquire Type |
Buyer |
Enquire Date |
Response Date Time |
| Online |
A |
01-01-2023 9:00am |
No response |
| Online |
B |
01-01-2023 9.00am |
01-01-2023 10.30am |
| Online |
B |
10-01-2023 10.00am |
10-10-2023 10.05am |
| Online |
C |
02-01-2023, 11am |
03-01-2023, 5pm |
| Online |
C |
03-01-2023,12pm |
03-01-2023, 5pm
|
| Online |
D |
03-01-2023, 11am |
03-01-2023, 5pm |
| Online |
D |
03-01-2023,12pm |
No response |
This is my load script provided in another forum but it only match 1 response to 1 enquiry.
temp:
load *,recno() as RowKey inline [
Date,Buyer,EnquireType,Type
01-01-2023 9:00am,A,Online,Enquire
01-01-2023 9.00am,B,Online,Enquire
10-01-2023 9.00am,B,Offline,Enquire
10-01-2023 10.00am,B,Online,Enquire
01-01-2023 10.30am,B,Offline,Response - Phone
10-10-2023 10.05am,B,Offline,Response - Phone
11-10-2023 11.00am,B,Offline,Response - Meetup
02-01-2023 11am,C,Online,Enquire
03-01-2023 12pm,C,Online,Enquire
03-01-2023 5pm,C,Offline,Response - Phone
03-01-2023 11am,D,Online,Enquire
03-01-2023 12pm,D,Online,Enquire
03-01-2023 5pm,D,Offline,Response - Phone
];
Main:
Load *,if(Buyer=peek('Buyer'),peek('EnqResKey')+1,1) as EnqResKey
Resident temp
Where wildmatch(Type,'Enquire*');
Left Join(Main)
Load Date as ResponseDate,Buyer,if(Buyer=peek('Buyer'),peek('EnqResKey')+1,1) as EnqResKey
Resident temp
Where wildmatch(Type,'Response*');
drop table temp;
exit Script;
