Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
priyalvp24
Creator
Creator

Autogenerate: generate count is negative

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.


error.png

9 Replies
tresesco
MVP
MVP

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
Creator
Creator
Author

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

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
Creator
Creator
Author

Hi surendra,

Its giving the below error now.

error.png

surendraj
Specialist
Specialist

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
Creator
Creator
Author

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

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
Partner - Creator
Partner - Creator

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