topic Re: a strange error - script error in QlikView

a strange error - script error

Mon, 22 Oct 2012 13:21:43 GMT

Hello guys and girls!!

I have worked on a qvw. and has recently just finished it. It works fine with data and stuff. But i wanted to try something, so after some days i wanted to do a reload, and then i got this error below, even though if i have not made any changes and it have been working before without errors:

Picture 1:

Picture 2:

Picture 3:

I dont know why this error comes, cause it has been working before....???

Re: a strange error - script error

Mon, 22 Oct 2012 13:25:40 GMT

Maybe i should say, that i perfectly loads all my data, but the only thing it says when i have gauge chart or mini charts is "Allocated memory exceeded"

Re: a strange error - script error

Mon, 22 Oct 2012 13:52:35 GMT

could it be something about my mastercalendar?? i use this script:

///******Create min/max variables*********

Let vMinDate = num(peek('Date_Clean', 0, 'DimDate'));

Let vMaxDate = num(peek('Date_Clean', -1, 'DimDate'));

Let vToday = num(Today());

//Let vToday = vMaxDate;

//*******Temp Calendar******

DateField:

Load

    date($(vMinDate) + RowNo() -1) as TempDate

AutoGenerate

    $(vMaxDate) - $(vMinDate) +1;

Re: a strange error - script error

danielact — Mon, 22 Oct 2012 13:55:20 GMT

Without seeing the whole script, it's going to be tough to figure this out. Can you paste the whole script in?

Re: a strange error - script error

Mon, 22 Oct 2012 13:56:27 GMT

yes i can!

///******Create min/max variables*********

Let vMinDate = num(peek('Date_Clean', 0, 'DimDate'));

Let vMaxDate = num(peek('Date_Clean', -1, 'DimDate'));

Let vToday = num(Today());

//Let vToday = vMaxDate;

//*******Temp Calendar******

DateField:

Load

date($(vMinDate) + RowNo() -1) as TempDate

AutoGenerate

$(vMaxDate) - $(vMinDate) +1;

MasterCalendar:

Load

TempDate as Date_Clean,

Week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Day(TempDate) as Day,

Weekday(TempDate) as WeekDay,

'Q' & ceil(month(TempDate) / 3) as Quarter,

Date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

Week(TempDate) & '-' & Year(TempDate) as WeekYear,

inyeartodate(TempDate, $(vToday),0) * -1 AS CurYTDFlag,

inyeartodate(TempDate, $(vToday),-1) * -1 AS LastYTDFlag,

inyear(TempDate, $(vToday), 0) * -1 AS CY, //Current year

inyear(TempDate, $(vToday), -1) * -1 AS FPY, //First prior year

inyear(TempDate, $(vToday), -2) * -1 AS SPY, //Second prior year

// Year-to-date flags

inyeartodate(TempDate, $(vToday), 0) * -1 AS CYTD, //Current year-to-date

inyeartodate(TempDate, $(vToday), -1) * -1 AS FPYTD, //First prior year-to-date

inyeartodate(TempDate, $(vToday), -2) * -1 AS SPYTD, //Second prior year-to-date

// Quarter flags

inquarter(TempDate, $(vToday), 0) * -1 AS CQ, //Current quarter

inquarter(TempDate, $(vToday), -4) * -1 AS FPQ, //First prior quarter, same quarter last year

inquarter(TempDate, $(vToday), -8) * -1 AS SPQ, //Second prior quarter, same quarter two years ago

// Quarter-to-date flags

inquartertodate(TempDate, $(vToday), 0) * -1 AS CQTD, //Current quarter-to-date

inquartertodate(TempDate, $(vToday), -4) * -1 AS FPQTD, //First prior quarter-to-date, same quarter last year

inquartertodate(TempDate, $(vToday), -8) * -1 AS SPQTD, //Second prior quarter-to-date, same quarter two years ago

// Month flags

inmonth(TempDate, $(vToday), 0) * -1 AS CM, //Current month

inmonth(TempDate, $(vToday), -1) * -1 AS PM, //Previous month

inmonth(TempDate, $(vToday), -12) * -1 AS FPM, //First prior month, same month last year

inmonth(TempDate, $(vToday), -24) * -1 AS SPM, //Second prior month, same month two years ago

// Month-to-date flags

inmonthtodate(TempDate, $(vToday), 0) * -1 AS CMTD, //Current month-to-date

inmonthtodate(TempDate, $(vToday), -1) * -1 AS PMTD, //Previous month-to-date

inmonthtodate(TempDate, $(vToday), -12) * -1 AS FPMTD, //First prior month-to-date, same month last year

inmonthtodate(TempDate, $(vToday), -24) * -1 AS SPMTD, //Second prior month-to-date, same month two years ago

// Week flags (use addmonths() to ensure proper week)

// Use inlunarweek() if the you want weeks (7-day periods) starting from January 1. This is different from the inweek() function

inweek(TempDate, $(vToday), 0) * -1 AS CW, //Current week

inweek(TempDate, addmonths($(vToday), -12), 0) * -1 AS FPW, //First prior quarter, same week last year

inweek(TempDate, addmonths($(vToday), -24), 0) * -1 AS SPW, //Second prior quarter, same week two years ago

// Week-to-date flags (use addmonths() to ensure proper week)

// Use inlunarweek() if the you want weeks (7-day periods) starting from January 1. This is different from the inweek() function

inweektodate(TempDate, $(vToday), 0) * -1 AS CWTD, //Current week-to-date

inweektodate(TempDate, addmonths($(vToday), -12), 0) * -1 AS FPWTD, //First prior week-to-date

inweektodate(TempDate, addmonths($(vToday), -24), 0) * -1 AS SPWTD, //Second prior week-to-date

// Day flags (use addmonths() to ensure proper day)

inday(TempDate, $(vToday), 0) * -1 AS CD, //Current day

inday(TempDate, addmonths($(vToday), -12), 0) * -1 AS FPD, //First prior quarter, same day last year

inday(TempDate, addmonths($(vToday), -24), 0) * -1 AS SPD, //Second prior quarter, same day two years ago

// Additional Week Flags

inweek(TempDate, $(vToday), -1) * -1 AS W1, //One week ago

inweek(TempDate, $(vToday), -2) * -1 AS W2, //Two weeks ago

inweek(TempDate, $(vToday), -3) * -1 AS W3, //Three weeks ago

inweek(TempDate, $(vToday), -4) * -1 AS W4, //Four weeks ago

inweektodate(TempDate, $(vToday), -1) * -1 AS W1TD, //One week ago to date

inweektodate(TempDate, $(vToday), -2) * -1 AS W2TD, //Two weeks ago to date

inweektodate(TempDate, $(vToday), -3) * -1 AS W3TD, //Three weeks ago to date

inweektodate(TempDate, $(vToday), -4) * -1 AS W4TD //Four weeks ago to date

RESIDENT DateField

ORDER BY TempDate ASC;

Drop table DateField;

Re: a strange error - script error

Mon, 22 Oct 2012 14:00:22 GMT

But i can tell you that my factall has around 3.000.000 rows. when it comes to the last ones it gives me the error!!

And it is just strange, cause i have loaded if before without errors!!!

Re: a strange error - script error

Mon, 22 Oct 2012 15:55:47 GMT

I just tryed to narrow the errors down - And it seems to be my autogenerator script that is makeing and error first. How come? It has been working, so why is it not working now???

Re: a strange error - script error

Mon, 22 Oct 2012 16:18:36 GMT

Hi there,

you are right, the autogenerate() generates an error. And thats correct because 40596-41171+1 is a negative value. So check your vMinDate and vmaxDate.

In fomer times it worked fine as your date-variables produced a positive difference.

HtH

Roland

Re: a strange error - script error

Mon, 22 Oct 2012 17:33:40 GMT

Thank you - But how come it suddenly returns a negative value???

My mindate in my dataset is 2011-01-01 and my maxdate is 2012-09-24

If nothing has changed - how come it suddenly will return a negative value??

Re: a strange error - script error

Mon, 22 Oct 2012 18:01:36 GMT

And how do i make it right??

This script allows me to run the whole thing without errors, but it does return me any dates - only 0 lines when i reload

DateField:

LOAD

Date($(vMinDate) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

What is wrong with it??

Re: a strange error - script error

Tue, 23 Oct 2012 09:03:43 GMT

Hi tjetjetje,

for me your script looks alright. Please check your date-values in the table "Date_Clean" to ensure to get allways a positive result for

. . .

Autogenerate ( $(vMaxDate) - $(vMinDate) +1);

Re: a strange error - script error

jonathandienst — Tue, 23 Oct 2012 09:31:39 GMT

Tjetjetje

I see that you are getting the min and max dates from DimDate. Perhaps something has caused the sort order of this table to change, so the the min date and max dates have swapped around.

You did not include the script that generated the DimDate table, so I cant be too specific, but I would do something like this to guarrantee the correct values:

MinMaxDates:

LOAD Max(Date) As MaxDate,

Min(Date) as MinDate

Resident DimDate;

Let vMaxdate = Peek('MaxDate')';

Let vMinDate = Peek('MinDate');

DROP Table MinMaxDates;

Change this as necessary to use the correct field and table names.

Hope that helps

Jonathan

Re: a strange error - script error

Tue, 23 Oct 2012 10:02:37 GMT

I forgot to order by DateKey in my DimDate - What a noob error!! But of cause I could not find it because it worked fine for me in the beginning!!

Thank you so much for all the answers and trying to solve the problem!!

I appreciate it very much!