Field not found in loading script

[Anonymous]

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();

[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 · Accepted solution]

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]

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]

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]

Hi Deva,

This worked perfect! Many thanks for the offered solution.

gr

Wim

[Anonymous]

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]

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

I'll also pay attention to the case sensitivity.

Wim

[pradosh_thakur]

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

[Anonymous]

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