End-of-week values column added to each day

[#1]

Hi guys,

I'm having hard time creating something like this:

base_date, week_num_overall, day_of_week, base_count, week_end_date, end of week base

2013-03-16, 65, 6, 44608, 2013-03-17, 44781

2013-03-17, 65, 7, 44781, 2013-03-17, 44781

2013-03-18, 66, 1, 45193, 2013-03-24, 46490

2013-03-19, 66, 2, 45455, 2013-03-24, 46490

2013-03-20, 66, 3, 45732, 2013-03-24, 46490

2013-03-21, 66, 4, 45997, 2013-03-24, 46490

2013-03-22, 66, 5, 46237, 2013-03-24, 46490

2013-03-23, 66, 6, 46375, 2013-03-24, 46490

2013-03-24, 66, 7, 46490, 2013-03-24, 46490

2013-03-25, 67, 1, 46794, 2013-03-31, 48437

2013-03-26, 67, 2, 47778, 2013-03-31, 48437

2013-03-27, 67, 3, 47598, 2013-03-31, 48437

2013-03-28, 67, 4, 47910, 2013-03-31, 48437

2013-03-29, 67, 5, 48175, 2013-03-31, 48437

2013-03-30, 67, 6, 48165, 2013-03-31, 48437

2013-03-31, 67, 7, 48437, 2013-03-31, 48437

only first and last columns are important here

I've got calendar with day_of_week column and table with base_count value for each day. I need to make on it the script level so I have date and the same base_count value from sunday for days 1-7, and then next value for whole week.

I could easily do that with following SQL:

select

c.full_date

,c.week_num_overall

,c.day_of_week

,b.base_count

,week_end_date

,(select B2.base_count from temp_active_base B2 where B2.base_date = c.week_end_date) as 'end of week base'

from temp_active_base b

join calendar c on b.base_date = c.full_date

order by 1

unfortunately the base_count values are something I load from QVD and I don't know how to put '(Select ...) as ... ' in Qlik script.

Can anyone help me here?

Thanks a lot!

Pawel

[yduval75]

LOAD

   week_num_overall

   day_of_week

   base_count

   week_end_date

from ..... (qvd);

[m_woolf · Accepted solution]

If you load the data and order by Base Date descending, you can do something like this:

If( week_num_overall = peek(' week_num_overall'),

     peek('end of week base'),

     base_count) as end of week base;

[#4]

Hi Guys,

thank for your quick ansers. I just came up with an idea that it is just a matter of:

load

distinct

week_num_overall, base_count
where day_of_week = 7

then join on week_num_overall

however, I'll try your approaches just for education benefits

Thanks so much!