Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Field not found in loading script

Hi,

I was making a master Calendar and when loading the script I got the error "Field 'a' not found". The script error appeared when I added the bold text. The first load statement of the bold text is the preceding load of the last load statement.

Does anyone sees what's wrong with the script?

regards,

Wim

Temp_Calendar_Range:

Load

num(floor(date(min(DATUM), 'dd/MM/yyyy'))) as MinDate,

  num(floor(date(max(DATUM), 'dd/MM/yyyy'))) as MaxDate

resident TelWerkgroepstats;

//Assign  the start and end dates to variables

let vMinDate = Peek('Mindate', 0, 'Temp_Calender_Range');

let vMaxDate = Peek('Maxdate', 0, 'Temp_Calender_Range');

drop table  Temp_Calendar_Range; //Cleanup

MasterCalendar:

LOAD DISTINCT

    Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

    Year(Temp_Date) as [Year],

    Month(Temp_Date) as [Month],

    Date(Temp_Date, 'YYYY-MM') as [Year - Month],

    'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

;

LOAD DISTINCT

    MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

    AUTOGENERATE (1)

    WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

//--- Remove the temporary variables

LET vMinDate = Null();

LET vMaxDate = Null();

1 Solution

Accepted Solutions
devarasu07
Master II
Master II

Hi,

You code looks ok except storing the date variable. just remember that qlik is case sensitive MinDate  & MaxDate

Temp_Calendar_Range:

Load

num(floor(date(min(DATUM), 'dd/MM/yyyy'))) as MinDate,

  num(floor(date(max(DATUM), 'dd/MM/yyyy'))) as MaxDate

resident TelWerkgroepstats;

//Assign  the start and end dates to variables

let vMinDate = Peek('MinDate', 0, 'Temp_Calender_Range');

let vMaxDate = Peek('MaxDate', 0, 'Temp_Calender_Range');

drop table  Temp_Calendar_Range; //Cleanup

MasterCalendar:

LOAD DISTINCT

    Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

    Year(Temp_Date) as [Year],

    Month(Temp_Date) as [Month],

    Date(Temp_Date, 'YYYY-MM') as [Year - Month],

    'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

;

LOAD DISTINCT

    MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

    AUTOGENERATE (1)

    WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

//--- Remove the temporary variables

LET vMinDate = Null();

LET vMaxDate = Null();

P.S: moving forward try to use that revised master calendar script (it's much faster than your current code.

Revised master calendar - store min/max date in... | Qlik Community

Better Calendar Scripts | Qlikview Cookbook

Thanks

Deva

View solution in original post

10 Replies
OmarBenSalem

Can you try as follow?

Let varMinDate = Num(Peek('MinDate', 0, 'Temp_Calendar_Range')); 

Let varMaxDate = Num(Peek('MaxDate', 0, 'Temp_Calendar_Range')); 

//DROP Table Temp_Calendar_Range; 

 

TempCalendar: 

LOAD 

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

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

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

drop table  Temp_Calendar_Range; //Cleanup

MasterCalendar:

LOAD DISTINCT

    Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

    Year(Temp_Date) as [Year],

    Month(Temp_Date) as [Month],

    Date(Temp_Date, 'YYYY-MM') as [Year - Month],

    'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

;

//--- Remove the temporary variables

LET varMinDate = Null();

LET varMaxDate = Null();

devarasu07
Master II
Master II

Hi,

You code looks ok except storing the date variable. just remember that qlik is case sensitive MinDate  & MaxDate

Temp_Calendar_Range:

Load

num(floor(date(min(DATUM), 'dd/MM/yyyy'))) as MinDate,

  num(floor(date(max(DATUM), 'dd/MM/yyyy'))) as MaxDate

resident TelWerkgroepstats;

//Assign  the start and end dates to variables

let vMinDate = Peek('MinDate', 0, 'Temp_Calender_Range');

let vMaxDate = Peek('MaxDate', 0, 'Temp_Calender_Range');

drop table  Temp_Calendar_Range; //Cleanup

MasterCalendar:

LOAD DISTINCT

    Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

    Year(Temp_Date) as [Year],

    Month(Temp_Date) as [Month],

    Date(Temp_Date, 'YYYY-MM') as [Year - Month],

    'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

;

LOAD DISTINCT

    MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

    AUTOGENERATE (1)

    WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

//--- Remove the temporary variables

LET vMinDate = Null();

LET vMaxDate = Null();

P.S: moving forward try to use that revised master calendar script (it's much faster than your current code.

Revised master calendar - store min/max date in... | Qlik Community

Better Calendar Scripts | Qlikview Cookbook

Thanks

Deva

Mark_Little
Luminary
Luminary

HI

If I remember right, I think with preceding loads the Distinct only work on the last part of load.

I would give the below a try.

MasterCalendar:

LOAD DISTINCT

    Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],

    Year(Temp_Date) as [Year],

    Month(Temp_Date) as [Month],

    Date(Temp_Date, 'YYYY-MM') as [Year - Month],

    'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]

;

LOAD

    MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

    AUTOGENERATE (1)

    WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);


Mark

Anonymous
Not applicable
Author

Hi Omar,

Thanks for the effort but I got the same error. Meanwhile other solutions where offered and they seem to work.

gr

Wim

Anonymous
Not applicable
Author

Hi Deva,

This worked perfect! Many thanks for the offered solution.

gr

Wim

Anonymous
Not applicable
Author

Hi Mark,

Your solution work also perfect but unfortunately I'm unable to mark two solutions as 'Correct answer'.

But, also many thanks to you for your time and effort!

gr

Wim

Anonymous
Not applicable
Author

Thanks also for the extra information about the revised master calendar.

I'll also pay attention to the case sensitivity.

Wim

pradosh_thakur
Master II
Master II

you can mark it as helpful ,you can mark as many as you want as helpful.

Learning never stops.
Anonymous
Not applicable
Author

Correct, but it seems only one answer can be marked as 'Correct answer'.