to mapped queries with next response

[elaineng]

I have a table with log of Buyer Activity. We would like to track whether each enquiry get a response for each online enquiry.

Date	Buyer	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 but is not giving the right answer:

BuyerActivity:

Load *
, IF(buyer= Peek(buyer) enquiretype = 'Online'  AND Match(Peek(type), 'Response - Phone', 'Response - Meetup') >0,  AND date < Peek(date), Peek(date),0)as [ResponseDateTime2]

[vinieme12]

As below

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;

[elaineng]

Hi @vinieme12 ,

the script were close, however, your script mapped 1 response to 1 enquiry, where the expected result is as long as the response date time is later then the enquiry date, it is consider the enquire responded.

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