Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wim_embrechts
New Contributor III

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
Honored Contributor II

Re: Field not found in loading script

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

10 Replies
OmarBenSalem
Esteemed Contributor

Re: Field not found in loading script

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
Honored Contributor II

Re: Field not found in loading script

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

mark6505
Valued Contributor III

Re: Field not found in loading script

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

wim_embrechts
New Contributor III

Re: Field not found in loading script

Hi Omar,

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

gr

Wim

wim_embrechts
New Contributor III

Re: Field not found in loading script

Hi Deva,

This worked perfect! Many thanks for the offered solution.

gr

Wim

wim_embrechts
New Contributor III

Re: Field not found in loading script

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

wim_embrechts
New Contributor III

Re: Field not found in loading script

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

I'll also pay attention to the case sensitivity.

Wim

pradosh_thakur
Valued Contributor III

Re: Field not found in loading script

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

wim_embrechts
New Contributor III

Re: Field not found in loading script

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

Community Browser