Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
azmeister3000
Contributor II
Contributor II

Give ID to all records between two dates

Hi community

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

InteractionDateOpportunity ID
Click01/01/2021 
Click02/01/2021 
Telephone Call03/01/2021 
Opened Opportunity04/01/20211
Click05/01/2021 
Click06/01/2021 
Click07/01/2021 
Click09/01/2021 
Click10/01/2021 
Opened Opportunity11/01/20212
Click12/01/2021 
Click13/01/2021 
Opened Opportunity14/01/20213
Telephone Call15/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

InteractionDateOpportunity IDOpportunity ID Interaction belongs To
Click01/01/2021 1
Click02/01/2021 1
Telephone Call03/01/2021 1
Opened Opportunity04/01/202111
Click05/01/2021 2
Click06/01/2021 2
Click07/01/2021 2
Click09/01/2021 2
Click10/01/2021 2
Opened Opportunity11/01/202122
Click12/01/2021 3
Click13/01/2021 3
Opened Opportunity14/01/202133
Telephone Call15/01/2021 4

 

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

Thanks

8 Replies
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;

 

Kushal_Chawda_0-1632097435679.png

 

azmeister3000
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

Thanks @Kushal_Chawda i mean like this

CompanyInteractionDateOpportunity IDOpportunity ID Interaction Belongs To
Company AClick01/01/2021 Company A1
Company AClick02/01/2021 Company A1
Company ATelephone Call03/01/2021 Company A1
Company AOpened Opportunity04/01/2021Company A1Company A1
Company BClick05/01/2021 Company B1
Company AClick06/01/2021 Company A2
Company BClick07/01/2021 Company B1
Company BClick09/01/2021 Company B1
Company BClick10/01/2021 Company B1
Company BOpened Opportunity11/01/2021Company B1Company B1
Company AClick12/01/2021 Company A2
Company AClick13/01/2021 Company A2
Company AOpened Opportunity14/01/2021Company A2Company A2
Company CTelephone Call15/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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

azmeister3000_0-1633082616294.png

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)