From daily to weekly data

[#1]

I've daily data (number of ...) and I would like to get data for the first day of the week. Data are as below:

day	week	number
01-gen	1	3
02-gen	1	6
03-gen	1	4
04-gen	1	9
05-gen	1	12
06-gen	1	32
07-gen	1	2
08-gen	2	6
09-gen	2	4
10-gen	2	8
11-gen	2	1
12-gen	2	0
13-gen	2	12
14-gen	2	4

and I would like to get only number 3 for week 1 and 6 for week 2 (first day of the week).

Thank you very much for your help,

r.

[#2]

You could probably do something in your load or in an expression.

In your load, you could add a flag field. Something like first_day and make it Y for the first days and N for everything else.

You could use a day of the week function to determine which days should be marked. In Oracle, you could use: to_char(day, 'D') to give you the day of the week. You could then use a CASE statement to, fo example, mark the 0s as first_day = Y and the rest N.

If you want to handle it in an expression, you can use the built in QlikView day of the week function. Within QlikView it is weekday(Date), which will return 0-6 for a numerical day of the week.

[#3]

Not quite sure if this is what you're looking for, but based on you'r original post I got this to work...

I had to change your 'day' column (01-gen, 02-gen...) into a date (01/Jan/2009, 02/Jan/2009...). Then I was able to use this expression to get what you are looking for:

=Sum( If(day=Aggr(Min(day),week), number))

The aggr function finds the minimum day for each week, then the if statement checks the day of each row to see if it is in the result set that Aggr() returned. If it is then it passes the value from the 'number' field to the Sum().

In aggregate I get a value of 9. If I throw in a week dimension, and break this by dimension, then for week 1 I get a value of 3 and for week 2 I get a value of 6, which I believe is what you wanted.

Hope this helps.

sjprows

[Anonymous]

r.,
Assuming that the "first day of the week" is Monday:

LOAD DISTINCT
 week,
 number
FROM <your table>
WHERE weekday(day)=0