Give ID to all records between two dates

[azmeister3000]

Hi community

I have some event log data and at certain points in the event log a new opportunity is created as below

Interaction	Date	Opportunity ID
Click	01/01/2021
Click	02/01/2021
Telephone Call	03/01/2021
Opened Opportunity	04/01/2021	1
Click	05/01/2021
Click	06/01/2021
Click	07/01/2021
Click	09/01/2021
Click	10/01/2021
Opened Opportunity	11/01/2021	2
Click	12/01/2021
Click	13/01/2021
Opened Opportunity	14/01/2021	3
Telephone Call	15/01/2021

I would like to create a field which categorises all interactions between opened opportunities which identifies those events as leading up to the opened opportunity as below

Interaction	Date	Opportunity ID	Opportunity ID Interaction belongs To
Click	01/01/2021		1
Click	02/01/2021		1
Telephone Call	03/01/2021		1
Opened Opportunity	04/01/2021	1	1
Click	05/01/2021		2
Click	06/01/2021		2
Click	07/01/2021		2
Click	09/01/2021		2
Click	10/01/2021		2
Opened Opportunity	11/01/2021	2	2
Click	12/01/2021		3
Click	13/01/2021		3
Opened Opportunity	14/01/2021	3	3
Telephone Call	15/01/2021		4

I've been looking at peek or intervalmatch or firstsortedvalue and not having much joy. Any ideas?

Thanks

[Kushal_Chawda]

@azmeister3000  try below

data:
LOAD Interaction, 
     Date, 
     [Opportunity ID]
FROM Table;

Final:
LOAD *,
     if(len(trim([Opportunity ID])),[Opportunity ID],
     if(len(trim(Previous([Opportunity ID]))),
     rangesum(Peek('Opportunity ID Interaction belongs To'),1),
     alt(Peek('Opportunity ID Interaction belongs To'),1))) as [Opportunity ID Interaction belongs To]
Resident data
Order by Date;

DROP Table data;

[azmeister3000]

Thanks @Kushal_Chawda  this works but actually realised that I should have added a field for "Company Name".  Does the data need to load in specific order for this to work?

[Kushal_Chawda]

@azmeister3000  did not get what you are trying to say. Would you be able to share example?

[azmeister3000]

Thanks @Kushal_Chawda i mean like this

Company	Interaction	Date	Opportunity ID	Opportunity ID Interaction Belongs To
Company A	Click	01/01/2021		Company A1
Company A	Click	02/01/2021		Company A1
Company A	Telephone Call	03/01/2021		Company A1
Company A	Opened Opportunity	04/01/2021	Company A1	Company A1
Company B	Click	05/01/2021		Company B1
Company A	Click	06/01/2021		Company A2
Company B	Click	07/01/2021		Company B1
Company B	Click	09/01/2021		Company B1
Company B	Click	10/01/2021		Company B1
Company B	Opened Opportunity	11/01/2021	Company B1	Company B1
Company A	Click	12/01/2021		Company A2
Company A	Click	13/01/2021		Company A2
Company A	Opened Opportunity	14/01/2021	Company A2	Company A2
Company C	Telephone Call	15/01/2021		Company C1

And I'm not sure if I'd need to load the data in a specific order?

Thanks

[Kushal_Chawda]

@azmeister3000  I am not sure if this is your actual data but based on this data logic will go as below.  To answer your question, yes, you need to sort your data to work with previous and peek logic.

data:
LOAD Company, 
     Interaction, 
     Date, 
     [Opportunity ID]
FROM Table;

Final:
LOAD *,
     purgechar(Company,'0123456789')&[Opportunity ID Interaction belongs To] as [Opportunity ID Interaction belongs To Final];
LOAD *,
     if(Company=Previous(Company), 
     if( len(trim([Opportunity ID])),keepchar([Opportunity ID],'0123456789'),
     if(len(trim(Previous([Opportunity ID]))),
     rangesum(Peek('Opportunity ID Interaction belongs To'),1),
     alt(Peek('Opportunity ID Interaction belongs To'),1))),1) as [Opportunity ID Interaction belongs To]
Resident data
Order by Company,Date;

DROP Table data;

DROP Field [Opportunity ID Interaction belongs To];

RENAME Field [Opportunity ID Interaction belongs To Final] to [Opportunity ID Interaction belongs To];

[azmeister3000]

Thanks @Kushal_Chawda this almost works - what's the reasoning for the purgechars and keepchars? is it possible to just use the Opportunity ID as it is in the original data?

[azmeister3000]

Also, one other slight issue with it is that when there is an opportunity ID present, the 

Opportunity ID Interaction Belongs To

field changes, when really it should be the same

[azmeister3000]

Sorry have to heavily redact the data but what you can see is that the Opportunity ID Interaction Belongs To seems to be adding a 1 when the new opportunity is recorded. Also, when there is an Opened Opportunity, the Opportunity ID Interaction Belongs To doesn't quite match (in this case there are two zeros at the beginning)