Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master III
Master III

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

View solution in original post

8 Replies
MK_QSL
MVP
MVP

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

Not applicable
Author

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

robert_mika
Master III
Master III

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

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Not applicable
Author

Yes , here is the data i use

sasiparupudi1
Master III
Master III

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
MVP
MVP

Use

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

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

robert_mika
Master III
Master III

try this for your date:

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

Not applicable
Author

Perfekt !