Why does this cause a field not found error?

Tue, 29 Jul 2014 15:55:32 GMT

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;

Re: Why does this cause a field not found error?

rwunderlich — Tue, 29 Jul 2014 16:31:15 GMT

Can you post the Document Log? That would be much easier to understand.

-Rob

Re: Why does this cause a field not found error?

Tue, 29 Jul 2014 16:59:01 GMT

I'd be glad to--where can one find the document log? Thanks!

Ben

Re: Why does this cause a field not found error?

its_anandrjs — Tue, 29 Jul 2014 17:49:13 GMT

You have to check the option in document properties that it is enabled or not by Click Settings >> Document Properties >> General Tab >> Check the "Generate Log File" Option checked or not if not then checked it and generate the log file by reloading the applicaion.

Re: Why does this cause a field not found error?

joshabbott — Tue, 29 Jul 2014 18:00:34 GMT

Just looking at the 'Filtered' year in the script, here is what might be the problem:

In the script you have these two lines:

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

If(Year(Today()) = FilteredYear....etc etc etc

You can't reference 'FilteredYear' in the line below where you are aliasing it. Replace 'FilteredYear' in the second line with the value of Filtered Year:

If(((Year(Date)<= Year(Today())) and (Year(Date)>2007)),Year(Date),Null())

Hope that helps!

Re: Why does this cause a field not found error?

Tue, 29 Jul 2014 18:21:32 GMT

Thanks, that worked!

topic Why does this cause a field not found error? in QlikView

Why does this cause a field not found error?

Re: Why does this cause a field not found error?

Re: Why does this cause a field not found error?

Re: Why does this cause a field not found error?

Re: Why does this cause a field not found error?

Re: Why does this cause a field not found error?