5 Replies Latest reply: Jul 29, 2014 2:21 PM by Ben Paulson RSS

    Why does this cause a field not found error?

      Dear Community,

      I've got a flag that I just put in a new field (FilteredYear) and when I do a reload, I get a field not found error with FilteredYear. (I think).  Why is this, and how can I fix it?

       

      Ben

       

       

      If(Year(Today())=FilteredYear And Num(Month(Today())-1)=Num(Month(MaxAsOfDate)),1,
      If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag,

       

       

       

      The full calendar script is below.

      --------------------------------------------------------------------------------------

      MAPPING LOAD

          
      Num(CalendarExclusions.Date) as CalendarExclusions.Date,

          
      CalendarExclusions.WorkingDayCounter

          

      FROM C:\QlikView\Development\QVD\Historical Analysis\SalesQVDLoad\CalendarExclusions.qvd (qvd);





      Set vDateSourceTable = 'LinkTable';

      Let vToday = num(Today());



      TempCalendar:

      Load Distinct

      Date(Daystart(Key|Date),'M/DD/YYYY') AS Date

      Resident $(vDateSourceTable);



      TempAccountValue:

      outer join (TempCalendar)

      Load

      Max(AccountValue.AsOfDate) as MaxAsOfDate

      Resident AccountValue;



      Calendar:

      LOAD Distinct

      Date(Daystart(Date),'M/DD/YYYY') AS Key|Date,

      Date,

         
      IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), 1),0) as WorkingDayFlag,

      //    IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), Date(Daystart(Date),'M/DD/YYYY')),Null()) as WorkingDayDate,

      // Basic Date Dimensions



      Year(Date) AS Year,

      Year(Date) - 1 AS LYear,

      Month(Date) AS Month,

      Num(Month(Date)) AS MonthNumber,

      // If(Year(Today())=Year(Date) And Num(Month(Today()))= $(vMaxAsOfDate),1,

      IF(((Year(Date)<=Year(Today())) and (Year(Date)>'2007')),Year(Date),Null()) as FilteredYear,

      If(Year(Today())=FilteredYear And Num(Month(Today())-1)=Num(Month(MaxAsOfDate)),1, // Num(Month(Date))

      If(Year(Today())>Year(Date) And Num(Month(Date))=12,1,0)) as ReportingPeriodFlag, //Flag for Sales Dash, originally to ignore selections on listboxes

      Week(Date) as WeekNumber,

      Text(Date(monthstart(Date), 'MMM-YYYY')) AS MonthYear,

      IF(Num(Weekday(Date))<5,Applymap('HolidayMap',Num(Date), 1),Null()) * week(Date) as Week,

      // Text(Date(monthstart(Date), 'YYYY')) & '-' & week(Date) AS YearWeek,

      If(Month(Date)>0,'Q' & ceil(Month(Date) / 3)) AS Quarter,

      If(Month(Date)>0,'Q' & ceil(Month(Date) / 3)) & '-' & Year(Date) AS QuarterYear,

      Ceil(Day(Date)/7,1) as MonthWeekNumber,



      IF(Ceil(Day(Date)/7,1)=1, '.6',

      IF(Ceil(Day(Date)/7,1)=2, '.85',

      IF(Ceil(Day(Date)/7,1)=3, '.85',1))) as Factor,



      // week(Date) AS Week,

      weekday(Date) AS Weekday,

      day(Date) AS Day,

      date(Date, 'MM/DD') AS DateMMDD,

         
      If(Date< AddMonths(Date(MonthStart(date(if(num(weekday(num($(vToday)))) = 0, num($(vToday))-2, num($(vToday)-1))))),-0)

         
      and Date> AddMonths(Date(MonthStart(date(if(num(weekday(num($(vToday)))) = 0, num($(vToday))-2, num($(vToday)-1))))),-12)-1,1,0) as L12M,



         
      If(Date< num($(vToday)) and Date> AddMonths(Date(MonthStart(date(if(num(weekday(num($(vToday)))) = 0, num($(vToday))-2, num($(vToday)-1))))),-12)-1,1,0) as R12M,



      // Year flags

      inyear(Date, $(vToday), 0) * -1 AS CY, //Current year

      inyear(Date, $(vToday), -1) * -1 AS FPY, //First prior year

      inyear(Date, $(vToday), -2) * -1 AS SPY, //Second prior year

      inyear(Date, $(vToday), 1) * -1 AS NY,     //Next year

      inyear(Date, $(vToday), -1) * -1 AS LY,     //Last year (Same as FPY)



      // Year-to-date flags

      inyeartodate(Date, $(vToday), 0) * -1 AS CYTD, //Current year-to-date

      inyeartodate(Date, $(vToday), -1) * -1 AS FPYTD, //First prior year-to-date

      inyeartodate(Date, $(vToday), -2) * -1 AS SPYTD, //Second prior year-to-date

      inyeartodate(Date, $(vToday), 1) * -1 AS NYTD,     //Next year



      // Quarter flags

      inquarter(Date, $(vToday), 0) * -1 AS CQ, //Current quarter

      inquarter(Date, $(vToday), -4) * -1 AS FPQ, //First prior quarter, same quarter last year

      inquarter(Date, $(vToday), -8) * -1 AS SPQ, //Second prior quarter, same quarter two years ago

      inquarter(Date, $(vToday), -1) * -1 AS LQ, //Last Quarter, 1 quarter ago

      inquarter(Date, $(vToday), -2) * -1 AS SLQ, //Second Last Quarter, 2 quarters ago

      inquarter(Date, $(vToday), -3) * -1 AS TLQ, //Third Last Quarter, 3 quarters ago

      inquarter(Date, $(vToday), 1) * -1 AS NQ, //Next quarter

      inquarter(Date, $(vToday), -1) * -1

      +
      inquarter(Date, $(vToday), -2) * -1 AS L2Q, //Last 2 Quarters





      // Quarter-to-date flags

      inquartertodate(Date, $(vToday), 0) * -1 AS CQTD, //Current quarter-to-date

      inquartertodate(Date, $(vToday), -4) * -1 AS FPQTD, //First prior quarter-to-date, same quarter last year

      inquartertodate(Date, $(vToday), -8) * -1 AS SPQTD, //Second prior quarter-to-date, same quarter two years ago

      inquartertodate(Date, $(vToday), -1) * -1 AS LQTD,     //Last Quarter, 1 quarter ago

      inquartertodate(Date, $(vToday), -2) * -1 AS SLQTD, //Second Last quarter-to-date, 2 quarters ago

      inquartertodate(Date, $(vToday), -3) * -1 AS TLQTD, //Third Last quarter-to-date, 3 quarters ago

      inquartertodate(Date, $(vToday), 1) * -1 AS NQTD, //Next quarter-to-date



      // Month flags

      inmonth(Date, $(vToday), 0) * -1 AS CM, //Current month

      inmonth(Date, $(vToday), -1) * -1 AS LM, //Last month

      inmonth(Date, $(vToday), -12) * -1 AS FPM, //First prior month, same month last year

      inmonth(Date, $(vToday), -24) * -1 AS SPM, //Second prior month, same month two years ago

      inmonth(Date, $(vToday), 1) * -1 AS NM, //Next month

          inmonth(Date, $(vToday), 2) * -1 AS SNM, //Second Next month, 2 Months in the future

          inmonth(Date, $(vToday), 3) * -1 AS TNM, //Third Next month, 3 Months in the future

          inmonth(Date, $(vToday), 4) * -1 AS FNM, //Fourth Next month, 4 Months in the future



      // Month-to-date flags

      inmonthtodate(Date, $(vToday), 0) * -1 AS CMTD, //Current month-to-date

      inmonthtodate(Date, $(vToday), -1) * -1 AS LMTD, //Last month-to-date

      inmonthtodate(Date, $(vToday), -12) * -1 AS FPMTD, //First prior month-to-date, same month last year

      inmonthtodate(Date, $(vToday), -24) * -1 AS SPMTD, //Second prior month-to-date, same month two years ago



      //  Months in Current Quarter flags

      IF((inquarter(Date, $(vToday), 0) * -1)=1 and MOD(Num(Month(Date)),3)=1,1,0) as CQM1,

      IF((inquarter(Date, $(vToday), 0) * -1)=1 and MOD(Num(Month(Date)),3)=2,1,0) as CQM2,

      IF((inquarter(Date, $(vToday), 0) * -1)=1 and MOD(Num(Month(Date)),3)=0,1,0) as CQM3,





      //  Months in Next Quarter flags

      IF((inquarter(Date, $(vToday), 1) * -1)=1 and MOD(Num(Month(Date)),3)=1,1,0) as NQM1,

      IF((inquarter(Date, $(vToday), 1) * -1)=1 and MOD(Num(Month(Date)),3)=2,1,0) as NQM2,

      IF((inquarter(Date, $(vToday), 1) * -1)=1 and MOD(Num(Month(Date)),3)=0,1,0) as NQM3,





      // Week flags

      inweek(Date, $(vToday), 0) * -1 AS CW, //Current week

      inweek(Date, $(vToday), -1) * -1 as LW, //Last week

          Inweek(Date, $(vToday), -52) * -1 as FPW, //Same week last year



      // Week-to-date flags

      inweektodate(Date, $(vToday), 0) * -1 AS CWTD, //Current week-to-date



      // Day Flags

      if(date(Date) = date($(vToday)), 1,0) as CD,

      if(date(Date) = date($(vToday)-1), 1,0) as Yesterday,

      if(date(Date) > date($(vToday)), 1,0) as FutureDate,

         
      IF(Date<= Num(AddMonths(MonthEnd(Today()),-3)),1,0) as [90DayFlag]





      Resident TempCalendar;



      Drop Table TempCalendar;