Autogenerate: generate count is negative

[priyalvp24]

Hello Qlik Community,

 I'm having a little bit of trouble with my master calendar script. I've reused this script in many of my applications without any trouble. Today I was developing a new application and it threw a script error after I inserted the master calendar code. A picture of the error is attached and my code is below. I don't understand why Qlik thinks there is a field in this part of the script. Has anyone ran into something similar to this? Thanks in advance!

temp_date:
Load
min(DocDate) as start_date,
Max(DocDate) as end_date
Resident Transformation1;

Let vstart_date=peek('start_date',0,temp_date);
Let vEnd_date=peek('end_date',0,temp_date);

//drop Table temp_date;

TempCalendar:
Load
date($(vstart_date) + rowno() - 1) As DocDate
Autogenerate
$(vEnd_date) - $(vstart_date) + 1;

MasterCalendar:
Load
DocDate,
num(Month(DocDate)) as DocMonth,
Month(DocDate) AS Doc_Month,
MonthName(DocDate) as Doc_Month_LM,
If(Month(DocDate) > 0 and Month(DocDate) < 4, 'FY-'&(Year(DocDate)-1)&'-'&Right(Year(DocDate),2),'FY-'&Year(DocDate)&'-'&Right((Year(DocDate)+1),2)) as DocYear_Filter,
If(Month(DocDate) > 0 and Month(DocDate) < 4, Year(DocDate)) as DocYear

resident TempCalendar;

DROP Table TempCalendar;

Please Help.

Regards,

Priyal.

[tresesco]

It seems that your date variables - vEnd_date and vstart_date are not being populated with values. If they get assigned with values, the error should go away.

[priyalvp24]

Thanks for your reply.
will you please send the expression for vEnd_date and vstart_date .
Regards,
Priyal

[surendraj]

Temp:

Load

               min(DocDate) as start_date,

               max(DocDate) as end_date

Resident Orders;

Let vstart_date= Num(Peek('start_date', 0, 'Temp'));

Let vend_date = Num(Peek('end_date', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

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

               Date($(vstart_date) + IterNo() - 1) as DocDate

               AutoGenerate 1 While $(vstart_date) + IterNo() -1 <= $(vend_date);

[priyalvp24]

Hi surendra,

Its giving the below error now.

[surendraj]

Might be the issue with  apostrophes.

Let vstart_date= Num(Peek('start_date', 0, 'Temp'));

Let vend_date = Num(Peek('end_date', 0, 'Temp'));

should be like this ' not like (’ )

check your script once!

[priyalvp24]

hi Surendra,

Its Not Working

[marcus_sommer]

Beside the already mentioned missing single-quotes around the tablename you should check if this table is generated because if there is an identically table before your load creates no new table else the data will be appended to the previous table (a solution might be a noconcatenate statement). Further check if your DocDate is really a date and not a timestamp or a string.

A quite helpful check if the variables contain the expected values is using trace, like:

TRACE $(vstart_date);

- Marcus

[surendraj]

Sometimes QlikView load the field names including some extra spaces or special characters . please make sure to load the script properly and debug the code to find out the peace of code where we got the actual error.

[Qlik_Administrator_Dude]

Just wanted to leave this somewhere. I had this error, and I had to format the date column in my CSV properly.