Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
RowID | SessionID | Price | Event | Flag |
---|---|---|---|---|
1 | 114 | 20 | itemclick | 0 |
2 | 114 | 50 | visit | 0 |
3 | 114 | 90 | Buy | 1 |
4 | 114 | 27 | Buy | 1 |
5 | 168 | 26 | Buy | 0 |
6 | 168 | 56 | Buy | 0 |
Hope you can help
Thanks in advance
like this ?
Thomas,
Try this. It is perhaps not optimum but it works:
TempSales:
LOAD RowNo,
SessionID,
Price,
Event
FROM [107132.xlsx]
(ooxml, embedded labels, table is Data);
Temp:
LOAD Distinct
SessionID as TempSession
Resident TempSales
Where upper(Event) = 'ITEMCLICK';
Sales:
NoConcatenate
LOAD RowNo,
SessionID,
Price,
Event,
upper(Event) = 'BUY' and exists(TempSession, SessionID) as Flag
resident TempSales;
DROP Tables TempSales, Temp
Fabrice;
Sweet - Can you maybe explain what it does in brief? How can i contorl if the flag should show -1 or 1 ? Currently its -1
What if i also want to flag all the itemclicks?
use if(xxx, 1, 0) as Flage
Fabrice
use either a OR statement
can try using also a Match or Mixmatch function
or Event <> 'VISIT'
Fabrice
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?
hii try this
load
RowNo,
SessionID,
Price,
Event,
if(Wildmatch(upper(Event),'BUY'),1,0) as Flag
FROM [107132.xlsx]
(ooxml, embedded labels, table is Data);
This will give me a flag on all rows containing event buy - Thats not what im looking for
Thomas,
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.
Fabrice