Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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;



1 Solution

Accepted Solutions
joshabbott
Contributor III

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

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!

5 Replies

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

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

-Rob

Not applicable

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

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

Ben

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

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.

joshabbott
Contributor III

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

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!

Not applicable

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

Thanks, that worked!

Community Browser