Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
LOAD
week_num_overall
day_of_week
base_count
week_end_date
from ..... (qvd);
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;
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!