Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
Not applicable

22 Replies
Not applicable

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

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

What if i also want to flag all the itemclicks?

Not applicable

use if(xxx, 1, 0) as Flage

Fabrice

Not applicable

use either a OR statement

can try using also a Match or Mixmatch function

or Event <> 'VISIT'

Fabrice


Not applicable

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?

Master II
Master II

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

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

Not applicable

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