3 Replies Latest reply: Jun 25, 2014 8:40 AM by Paweł Sowa RSS

    End-of-week values column added to each day

    Paweł Sowa

      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