So i got this issue, and i simply cannot figure out how to manage this.
As in my previous post i want to find the row where there has been a BUY event when there also have been an Itemclick event within the same session.
Notice: It should be in the script and not in a expression (i think its too heavy)
Hope you can help
Thanks in advance
Try this. It is perhaps not optimum but it works:
(ooxml, embedded labels, table is Data);
SessionID as TempSession
Where upper(Event) = 'ITEMCLICK';
upper(Event) = 'BUY' and exists(TempSession, SessionID) as Flag
DROP Tables TempSales, Temp
So something like this:
if(upper(Event) <> 'VISIT' and exists(TempSession, SessionID)=-1,
if(upper(Event) = 'ITEMCLICK'
,-1,1)) as Flag
Can you explain what this means:
exists(TempSession,SessionID) ? What does it do and where does it look?
It looks for tempsession which exists in sessionid from? or?
In fact, you want to put some data (1) into a flag depending on another field.If not 0 or anything else.
In the given example, I see SessionID 114 gets the number 1 because there is a field SessionID 114 and Event ItemClick, The other sessionID 168 gets 0 because there is no Itemclick Event. The Event BUY is common to both but it is not because you get a BUY that you get 1.
So what I am doing is to get a list of these SessionID that have an ItemClick Event (TempSession is a field storing that list)
Afterwards, with Exists() function, I may test the current SessionID compared to that list.
because the event ItemClick does not get 1 in the flag, I test the presence of the sessionID in the list (through Exist) and the type of Event.