Help with Master calendar script error in the Qlik Sense

[#1]

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;

[MK_QSL]

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

[#3]

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

[robert_mika]

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

Feeling Qlikngry?

How To /Missing Manual(18 articles)

[#5]

Yes , here is the data i use

[sasiparupudi1 · Accepted solution]

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

[MK_QSL]

Use

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

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

[robert_mika]

try this for your date:

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

[#9]

Perfekt !