Invalid Autogenerate count

[#1]

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;

Kindly Help me,

Regards,

Anjaneyulu

[petter · Accepted solution]

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

[#3]

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