Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

How to flag rows in script based on events?

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)

RowIDSessionIDPriceEventFlag
111420itemclick0
211450visit0
311490Buy1
411427Buy1
516826Buy0
616856Buy0

Hope you can help

Thanks in advance

Tags (3)
1 Solution

Accepted Solutions
Not applicable

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

like this ?

22 Replies
Not applicable

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

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;

Not applicable

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

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

Not applicable

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

What if i also want to flag all the itemclicks?

Not applicable

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

use if(xxx, 1, 0) as Flage

Fabrice

Not applicable

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

use either a OR statement

can try using also a Match or Mixmatch function

or Event <> 'VISIT'

Fabrice


Not applicable

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

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
Honored Contributor II

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

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

Not applicable

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

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

Not applicable

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

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