Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Help with Master calendar script error in the Qlik Sense

Hi Im trying to create a master calendar script in qlik sense but i get the following error :

What is the source of problem here ?

Error Message :

Started loading data

Test << data Lines fetched: 1 000 QuartersMap << AUTOGENERATE(12) Lines fetched: 12 Temp << Test Lines fetched: 1

The following error occurred:

Field not found - <<=>

The error occurred here:

TempCalendar: LOAD + Iterno()-1 As Num, Date( + IterNo() - 1) as TempDate AutoGenerate 1 While + IterNo() -1 <=

My Script :

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 kr;-#.##0,00 kr';

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]';

SET MonthNames='jan;feb;mar;apr;maj;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='mån;tis;ons;tor;fre;lör;sön';

SET LongMonthNames='januari;februari;mars;april;maj;juni;juli;augusti;september;oktober;november;december';

SET LongDayNames='måndag;tisdag;onsdag;torsdag;fredag;lördag;söndag';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='sv-SE';

Test:

LOAD

    id,

    firstname,

    surname,

    email,

    country,

    city,

    Longitude,

    Latitude,

    GeoMakePoint(Latitude,Longitude) as GeoKey,

    title,

    nhours,

    amount,

    dates

FROM [lib://new/MOCK_DATA(2).xlsx]

(ooxml, embedded labels, table is data);

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(dates) as minDate,

max(dates) as maxDate     

Resident [Test];

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

Let vToday = Today();

Drop Table Temp;

TempCalendar:

LOAD

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

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

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

Calendar:

Load

               TempDate AS Date,

               TempDate as CalcDate,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               if(TempDate <= Today(),if(TempDate >= (Today()-365),1,0),0) as RC12,

               if(TempDate <= Today(),if(TempDate >= (Today()-182),1,0),0) as RC6,

               date(monthstart(TempDate), 'MM-YYYY') as MonthYear,

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               MonthName(TempDate) as MonthName

Resident TempCalendar;

DROP Table TempCalendar;

1 Solution

Accepted Solutions
sasiparupudi1
Honored Contributor III

Re: Help with Master calendar script error in the Qlik Sense

Change the bold part

Test:

LOAD

    id,

    firstname,

    surname,

    email,

    country,

    city,

    Longitude,

    Latitude,

    GeoMakePoint(Latitude,Longitude) as GeoKey,

    title,

    nhours,

    amount,

     date#(dates,'YYYY/MM/DD') as dates

FROM [lib://new/MOCK_DATA(2).xlsx]

(ooxml, embedded labels, table is data);

hth

Sasi

8 Replies

Re: Help with Master calendar script error in the Qlik Sense

What is the Format of dates... Looks like varMinDate and varMaxDate is not getting created...

Not applicable

Re: Help with Master calendar script error in the Qlik Sense

I have not formatted it yet, im trying with Date(  ) function, still wont work

Re: Help with Master calendar script error in the Qlik Sense

Post a sample of your data if you still struggling..

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Not applicable

Re: Help with Master calendar script error in the Qlik Sense

Yes , here is the data i use

sasiparupudi1
Honored Contributor III

Re: Help with Master calendar script error in the Qlik Sense

Change the bold part

Test:

LOAD

    id,

    firstname,

    surname,

    email,

    country,

    city,

    Longitude,

    Latitude,

    GeoMakePoint(Latitude,Longitude) as GeoKey,

    title,

    nhours,

    amount,

     date#(dates,'YYYY/MM/DD') as dates

FROM [lib://new/MOCK_DATA(2).xlsx]

(ooxml, embedded labels, table is data);

hth

Sasi

Re: Help with Master calendar script error in the Qlik Sense

Use

Date(Date#(dates,'YYYY/MM/DD')) as dates

while loading from your data... and it should work now..

Re: Help with Master calendar script error in the Qlik Sense

try this for your date:

Interval#( dates,'YYYY/MM/DD') as dates

Not applicable

Re: Help with Master calendar script error in the Qlik Sense

Perfekt !