topic Re: How to flag rows in script based on events? in QlikView

How to flag rows in script based on events?

Wed, 12 Feb 2014 08:58:07 GMT

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

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 09:21:12 GMT

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;

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 09:38:44 GMT

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

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 09:50:32 GMT

What if i also want to flag all the itemclicks?

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 10:03:45 GMT

use if(xxx, 1, 0) as Flage

Fabrice

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 10:05:12 GMT

use either a OR statement

can try using also a Match or Mixmatch function

or Event <> 'VISIT'

Fabrice

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 10:15:19 GMT

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?

Re: How to flag rows in script based on events?

er_mohit — Wed, 12 Feb 2014 10:18:54 GMT

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

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 10:23:29 GMT

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

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 10:28:05 GMT

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

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 10:49:51 GMT

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

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 10:52:02 GMT

Aint working 😕

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 10:57:31 GMT

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

Re: How to flag rows in script based on events?

Wed, 12 Feb 2014 11:20:53 GMT

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

Re: How to flag rows in script based on events?

Wed, 19 Feb 2014 16:30:21 GMT

Thomas,

That should do it

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

Fabrice;

Re: How to flag rows in script based on events?

Wed, 19 Feb 2014 16:41:15 GMT

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.

Re: How to flag rows in script based on events?

Wed, 19 Feb 2014 17:13:54 GMT

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

Re: Re: How to flag rows in script based on events?

Wed, 19 Feb 2014 17:39:18 GMT

like this ?

Re: Re: How to flag rows in script based on events?

Wed, 19 Feb 2014 17:48:12 GMT

yep - thanks for that almost had it!

Re: Re: Re: How to flag rows in script based on events?

Wed, 19 Feb 2014 20:05:44 GMT

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.