Rank for each day

[#1]

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?

Br

Daniel

[prieper]

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

[#3]

Hi,

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

BR

Daniel

[#4]

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?

[#5]

that is correct. 1000 posts for each day.

[prieper]

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

[#7]

I have this code:

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

traffic:

load

time_event,

search

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?

br

/D

[#8]

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;