11 Replies Latest reply: Mar 15, 2017 12:55 AM by Anjali Ahlawat RSS

    Week creation

    Anjali Ahlawat

      I am using below script to create WeekNoAndMonth:

       

      Common:
      load *,
      MonthName(CommonDate) & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth;

       

      load *,
      if((week(CommonDate) - week(monthstart(CommonDate)) + 1)<1,1,(week(CommonDate) - week(monthstart(CommonDate)) + 1)) as WeekNumberInMonth;

       

      LOAD *,
      MakeDate(CommonYear,CommonMonth,CommonDays) as CommonDate

      FROM
      $(AppQvdPath)Common.qvd
      (qvd);

       

      WeekNoAndMonth is the dimension that I am using at last in the chart.


      Requirement:

      I want to add one more field here in script level only that can show me the last 8 weeks as values like


      MonthName  & '- Week' & ' ' & WeekNumberInMonth but the week start is friday and week end is Thursday.


      Capture.JPG

      but here my weeks formation should be like:

      Dimension:


      Jan 2017 - Week 1: 6th to 12th feb

      Jan 2017 - Week 2: 13th to 19th feb

      Jan 2017 - Week 3: 20th to 26th feb

      Jan 2017 - Week 4: 27th to 2nd feb

      Feb 2017 - Week 1: 3rd to 9th feb

      Feb 2017 - Week 2: 10th ro 16th feb

      Feb 2017 - Week 3:  17th to 23rd feb


      Thanks in advance

        • Re: Week creation
          Anil Babu

          Have you look this?

           

          Redefining the Week Numbers

            • Re: Week creation
              Anjali Ahlawat

              Hi Avinash,

               

              I have already redefined the week start in script.

              The requirement is I want to create the dimension in the same script that I am using on top to create the last 8 weeks.

               

              I need the below weeks:

              Jan 2017 - Week 1: 6th to 12th feb

              Jan 2017 - Week 2: 13th to 19th feb

              Jan 2017 - Week 3: 20th to 26th feb

              Jan 2017 - Week 4: 27th to 2nd feb

              Feb 2017 - Week 1: 3rd to 9th feb

              Feb 2017 - Week 2: 10th ro 16th feb

              Feb 2017 - Week 3:  17th to 23rd feb


              the dimension i have used in my script is creating weeks like:

              Jan 2017 - Week 1: 6th to 12th feb

              Jan 2017 - Week 2: 13th to 19th feb

              Jan 2017 - Week 3: 20th to 26th feb

              Jan 2017 - Week 4: 27th to 31st jan

              Feb 2017 - Week 1: 1st to 2nd feb

              Feb 2017 - Week 2: 3rd to 9th feb

              Feb 2017 - Week 3: 10th ro 16th feb

              Feb 2017 - Week 4:  17th to 23rd feb

               

              Hope you understood the requirement.

               

              I need two dimensions to show monthly and weekly  reports as there week formations are different.


              please help asap.

               

              Thnks

            • Re: Week creation
              Chanty 4u

              Hi Anjali,

               

              Even am facing the similar kind of issue

               

              Re: RE:MonthEnd

              • Re: Week creation
                Ravindra Nadh

                raw_data:

                LOAD date(@1) as CommonDate,

                Week(@1) as WeekNum,

                Date(WeekStart(@1)+4) as WeekStartDate,

                date(WeekEnd(@1)+4) as WeekEndDate

                FROM

                [C:\Users\TEMP.NOE.002\Desktop\calendar.txt]

                (txt, codepage is 1252, no labels, delimiter is '\t', msq);

                 

                 

                Intervals:

                load WeekStartDate, WeekEndDate Resident raw_data;

                 

                 

                temp:

                IntervalMatch(CommonDate) LOAD WeekStartDate, WeekEndDate Resident Intervals;

                left join

                load WeekStartDate, WeekNum, MonthName(WeekStartDate) as WSMonth Resident raw_data;

                 

                 

                Agg_table:

                load WSMonth, Min(WeekNum) as MSWN resident temp group by (WSMonth) ;

                 

                 

                temp2:

                load CommonDate, WeekStartDate, WeekNum, WSMonth Resident temp;

                left join

                load WSMonth, MSWN Resident Agg_table;

                 

                 

                Final:

                load * , MonthName(WeekStartDate) &'-'&(mod(WeekNum,MSWN)+1) as Month_Week

                Resident temp2;

                 

                 

                drop Table raw_data,Intervals,Agg_table, temp,temp2;

                 

                1.PNG

                  • Re: Week creation
                    Anjali Ahlawat

                    Hi Ravindra,

                     

                    Thank you so much for your help.

                     

                    I am just thinking how will i link this script to the script I have shared as I have to use both.

                     

                    Thanks a ton for replying.I,l try and will post again if this works.

                    • Re: Week creation
                      Anjali Ahlawat

                      Hi Ravindra,

                       

                      Thanks a lot for this script and it is giving me the exact desired results that I want.

                       

                      But in my existing application.

                       

                      I am using a code like this and I want to merge your code with this table:

                      Common:

                      load *,

                      MonthName(CommonDate)  & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth_MonthlyReports;

                      load *,

                      if((week(CommonDate) - week(monthstart(CommonDate)) + 1)<1,1,(week(CommonDate) - week(monthstart(CommonDate)) + 1)) as WeekNumberInMonth;

                      LOAD *,

                      MakeDate(CommonYear,CommonMonth,CommonDays) as CommonDate

                      FROM

                      $(AppQvdPath)Common.qvd

                      (qvd);

                       

                      And I want the new week number that you have formed to get linked with the above code.

                      So i wrote the above code first and then used ur code below that.But when I am reloading the system hangs.

                      Is it becuse of synthetic keys or some other issue is there.

                      I tried to use qualify * as well but that too dint help.


                      See the code I tried :

                      //creating month names and week names here

                      Common:

                      load *,

                      MonthName(CommonDate)  & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth_MonthlyReports;

                      load *,

                      if((week(CommonDate) - week(monthstart(CommonDate)) + 1)<1,1,(week(CommonDate) - week(monthstart(CommonDate)) + 1)) as WeekNumberInMonth;

                      LOAD *,

                      MakeDate(CommonYear,CommonMonth,CommonDays) as CommonDate

                      FROM

                      $(AppQvdPath)Common.qvd

                      (qvd);

                      //============================================================================================//

                      qualify *;

                      CommonTemp:

                      load

                      CommonDate,

                      CommonWeeks as WeekNum,

                      Date(WeekStart(CommonDate)+4) as WeekStartDate,

                      date(WeekEnd(CommonDate)+4) as WeekEndDate

                      Resident Common;

                       

                      Intervals:

                      load CommonTemp.WeekStartDate, CommonTemp.WeekEndDate Resident CommonTemp;

                       

                      temp:

                      IntervalMatch(CommonTemp.CommonDate) LOAD CommonTemp.WeekStartDate, CommonTemp.WeekEndDate

                      Resident Intervals;

                      left join

                      load CommonTemp.WeekStartDate, CommonTemp.WeekNum, MonthName(CommonTemp.WeekStartDate) as WSMonth Resident CommonTemp;

                       

                      Agg_table:

                      load WSMonth, Min(CommonTemp.WeekNum) as MSWN resident temp group by (WSMonth) ;

                       

                      temp2:

                      load CommonTemp.CommonDate, CommonTemp.WeekStartDate, CommonTemp.WeekNum, WSMonth Resident temp;

                      left join

                      load WSMonth, MSWN Resident Agg_table;

                       

                      Final:

                      load CommonTemp.CommonDate ,

                      MonthName(CommonTemp.WeekStartDate) &'-'&(mod(CommonTemp.WeekNum,MSWN)+1) as Month_Week

                      Resident temp2;

                       

                      drop Tables CommonTemp,Intervals,Agg_table, temp,temp2;

                       

                      Please help.