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]

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;

[#3]

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

[#4]

What if i also want to flag all the itemclicks?

[#5]

use if(xxx, 1, 0) as Flage

Fabrice

[#6]

use either a OR statement

can try using also a Match or Mixmatch function

or Event <> 'VISIT'

Fabrice

[#7]

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?

[er_mohit]

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);

[#9]

This will give me a flag on all rows containing event buy - Thats not what im looking for

[#10]

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