How to flag rows in script based on events?

[#1]

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

[#2]

Try this code:

Table:

LOAD RowID,

     SessionID,

     Price,

     Event,

     Flag,

     if(SessionID=SessionID,if(Match(Event,'Buy','itemclick')<>0,RowID)) as Output

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

[#3]

Aint working 😕

[#4]

First click on a particular session and then check the Output. I already checked, its working fine

[#5]

Nope - It also takes sessions where there is no item.

[#6]

Thomas,

That should do it

I have created a field with 2 fields, and I do an exists() on it

Fabrice;

[#7]

Brilliant Aunez! Thank you so much. I was trying to see if i could get a table which only consisted of sessions which had a buy and itemclick. But i cannot seem to figure out how i would get a session table where it consists of session that have both itemclick and buy with same productids.

[#8]

What would you do if it should look like this?

RowID	SessionID	Price	Event	ProductID	Flag	FlagProductBuyItemClickBuy
1	114	20	itemclick	1	-1	1
2	114	50	visit	2	0	0
3	114	90	Buy	2	3	0
4	114	27	Buy	1	1	0
5	168	26	Buy	2	0	0
6	168	56	Buy	1	0	0
7	114	23	Basket	4	0	0
8	155	34	itemclick	1	-1	1
9	155	46	visit	1	0	0
10	155	46	basket	4	0	0
11	155	34	itemclick	2	-1	1
12	155	34	visit	2	0	0
13	155	34	Buy	2	1	0
14	155	34	Buy	1	1	0
15	155	34	buy	3	3	0
16	133	34	Itemclick	6	-1	0
17	133	34	buy	3	3	0
18	145	34	Itemclick	3	2	0
19	145	34	visit	3	0	0

I have tried this code:

if(upper(Event) = 'ITEMCLICK', if(exists(TempSessionBuy,SessionID),1,0)) as FlagItemBuy

but i also get 1 on those where there is a buy but they have not the same productid

[#9 · Accepted solution]

like this ?

[#10]

yep - thanks for that almost had it!

[#11]

Hi - as you can see in this excel ark - i have a new flag called FlagProductBuyLater

The flag should be 1, when the same cookieid has bought the same productid there has been an itemclick regardless of what session it is in.

So cookieid 1 has an itemclick in session 114 on productid 8 but buys it first in session 178 on same productid 8 - then that line should have 1 in flag as illustrated in the excel file.