10 Replies Latest reply: Oct 31, 2014 11:13 AM by Alex Wang RSS

    error when try to use count in proceeding load

    Alex Wang

      Hi,

       

      I try to use count distinct function calculate how many weeks in one month, I add this in a proceeding load script as below,

       

      mapQuarterName:

      Mapping LOAD * INLINE [

          mapQuarter, mapQuartername

          1, jan-mar

          2, apr-jun

          3, jul-sep

          4, oct-dec

      ];

       

      Let varMinDate = 41275;//calendar start from 2013-01-01 

      Let varMaxDate = 43465;//calendar end to 2018-12-31   

       

      TempCalendar: 

      LOAD 

                     $(varMinDate) + Iterno()-1 As Num, 

                     Date($(varMinDate) + IterNo() - 1) as TempDate 

                     AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

       

      DATE: 

      LOAD//Proceeding Load create NoOfWeek

         *,

         count(distinct(yearweek)) AS NoOfWeek

      Group By yearmonth;

      LOAD

                     TempDate AS Date, 

                     Year(TempDate) As year, 

          Ceil(Month(TempDate)/3) AS quarter,

          ApplyMap('mapQuarterName',Ceil(Month(TempDate)/3)) &' '& Year(TempDate) AS quartername ,

                     date(monthstart(TempDate), 'YYYYMM') AS yearmonth, 

                     WeekYear(TempDate)*100+Week(weekstart(TempDate)) as yearweek

      Resident TempCalendar 

      Order By TempDate ASC; 

      Drop Table TempCalendar;

       

      When I run it, I got error message, can I use a count function in proceeding load? how?

      2014-10-31 15-48-47.jpg

      Thanks a lot!

      alex