topic Rank for each day in QlikView

Rank for each day

Mon, 14 Sep 2009 22:38:35 GMT

Hi,

I have a table with two columns. One column is date and the other is searchword. The table is 23 million rows for 4 month of data witch mean i have alot of data. To narrow it down i wish to only retrive the 1000 most common searchword for each day, how can i do that?

Daniel

Rank for each day

prieper — Tue, 15 Sep 2009 03:31:29 GMT

Hi,

have no ready solution but think the below should do:

Load data grouped by date and criteria and Count(Criteria)
Sort by the Count DESC
Write into new table, adding RecNo()
Filter by the topmost n RecNo()

Peter

Rank for each day

Tue, 15 Sep 2009 13:10:38 GMT

Hi,

I have done that, but then i get the most popular suarches for the whole period and not on day-level.

Daniel

Rank for each day

Tue, 15 Sep 2009 14:02:26 GMT

Do you want to retrieve 1000 kewords for each day. ie if you have 10 days data, then you will get 10*1000 keywords. Is that what you want?

Rank for each day

Tue, 15 Sep 2009 14:38:29 GMT

that is correct. 1000 posts for each day.

Rank for each day

prieper — Tue, 15 Sep 2009 15:22:10 GMT

Hi,

instead of the RecNo() you then should work with PEEK-function, something like

IF(PEEK(MyDate) = MyDate, PEEK(RankPerDay) + 1, 1) AS RankPerDay

HTH
Peter

Rank for each day

Tue, 15 Sep 2009 16:04:02 GMT

I have this code:

Normal 0 21 false false false SV X-NONE X-NONE MicrosoftInternetExplorer4

traffic:

load

time_event,

FROM

QVD\Traffic_event.qvd (qvd);

traffic2:

load

time_event,

search,

count(name) as count

resident traffic

group by time_event, search;

drop table traffic;

But then i want to just retrive the 1000 most common earches for each day. Could peek work?

Rank for each day

Tue, 15 Sep 2009 17:53:36 GMT

The following solution will retrieve the whole values from database initially and will remove the unwanted values from QlikView.

TempKeywords:
Load Date,
Keyword,
NumberOfSearch,
if(DATE=peek(DATE),peek(rank)+1,1)as rank;
SQL Select count(*) as NumberOfSearch, Keyword ,Date FROM KEYWORD_TABLE
GROUP BY Date , Keyword ORDER BY Date , count(*) DESC;

Keywords:
Load Date,
Keyword,
NumberOfSearch,
rank
Resident TempKeywords
where rank<=1000;
Drop table TempKeywords;