Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Invalid Autogenerate count

Hi All,

I have loaded a orders table in QlikSence .My Script is

LIB CONNECT TO 'C--Users-Anjaneyulu-Desktop-LMS-New folder-QlikView Training-Developer-Training Course Files-DeveloperI-Datasources-QWT.mdb';

Orders:

LOAD CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    OrderID,

    ShipperID;

SQL SELECT CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    OrderID,

    ShipperID

FROM Orders;

Let vMinDate= Num(Peek('OrderDate',0,'Orders'));

Let vMaxDate= Num(Peek('OrderDate',-1,'Orders'));

DateFiled:

Load

$(vMinDate)+RowNo()-1 as Num,

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

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

I want to create  Date Filed, but i am getting Invalid AutoGenerate count like below;DateField Error.JPG

Kindly Help me,

Regards,

Anjaneyulu

Tags (1)
1 Solution

Accepted Solutions
petter
Not applicable

Re: Invalid Autogenerate count

It is important that you have an ORDER-clause in your SELECT statement so you get the dates in ascending order. You can rely on the table being in sorted order by date without specifying it.

Your SQL should then look like this:

LOAD CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    OrderID,

    ShipperID;

SQL SELECT CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    OrderID,

    ShipperID

FROM Orders

ORDER BY OrderDate

;

Then the Peek()-functions can deliver the minimum and maximum value guaranteed. Without ORDER you run the risk of getting a newer date as minimum and an older date as maximum and you run into the trouble you show us...

2 Replies
petter
Not applicable

Re: Invalid Autogenerate count

It is important that you have an ORDER-clause in your SELECT statement so you get the dates in ascending order. You can rely on the table being in sorted order by date without specifying it.

Your SQL should then look like this:

LOAD CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    OrderID,

    ShipperID;

SQL SELECT CustomerID,

    EmployeeID,

    Freight,

    OrderDate,

    OrderID,

    ShipperID

FROM Orders

ORDER BY OrderDate

;

Then the Peek()-functions can deliver the minimum and maximum value guaranteed. Without ORDER you run the risk of getting a newer date as minimum and an older date as maximum and you run into the trouble you show us...

Not applicable

Re: Invalid Autogenerate count

Dear Petter,

Thanks a lot for your reply with a perfect resolution. It's correct answer and it's working for me.

You made my day!!

Thanks

Ajay