Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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...
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...
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