Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I am unable to load the required data. I am trying to filter it based on the date.
I want to load the date from a specific date defined by a variable ,
I have data from Nov 2007 to July 2011.
I want data starting from Jan 2008, hence I'm storing this in variable vdate.
My code is as follows,
Let vdate = Addmonths(vCurrentDate, -($(vCurrentMonth)+35),'MMM-YYYY');
REM LOAD OrderID,
CustomerID,
EmployeeID,
Freight,
OrderDate,
ShipperID;
SQL SELECT OrderID,
CustomerID,
EmployeeID,
Freight,
OrderDate,
ShipperID
FROM "C:\WINDOWS\QWT.mdb"."Orders"
WHERE OrderDate >= $(vdate);
---------------------------------------------------------------------------------------------------------------
Hi,
what is the format of the OrderDate?
the format of the OrderDate and vdate should be same...
try something like this.
Let vdate=Date(MakeDate(2008,01,01),'DD/MM/YYYY'); // should be same format as OrderDate
Check date formats of both vdate and OrderDate ...
Use Saingle Quotes in your where clause and make sure the format of two dates are same
WHERE OrderDate >= '$(vdate)';
As everyone have suggested. Check on the date formula/format.
I would suggest, take the where clause to the Qlik Script from SQL script. You might load a lot of data/ extraction might be heavier but it will work for sure.
Try
If you dates are in the format 'Jan-2018', please change the format accordingly
let vCurrentMonth=Num(Month(Today()));
Let vdate = Date(Addmonths(today(), -($(vCurrentMonth)+35)),'MMM YYYY');
REM LOAD OrderID,
CustomerID,
EmployeeID,
Freight,
OrderDate,
ShipperID;
SQL SELECT OrderID,
CustomerID,
EmployeeID,
Freight,
OrderDate,
ShipperID
FROM "C:\WINDOWS\QWT.mdb"."Orders"
WHERE OrderDate >= '$(vdate)';
I'm getting the following error, when i edit it to >='$(vdate)';
Can you please help?
hi,
can you please look into it?
Which script is giving you error?
please share the data in the field OrderDate and the value in vDate
also, please share the application log
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
//To load the data for current 3 years backward to Jan
Let vCurrentMonth = num(month(vCurrentDate));
Let vdate = Addmonths(vCurrentDate, -($(vCurrentMonth)+35),'MMM-YYYY');
//Let vnew_date = Date#(vdate) < vCurrentDate;
LIB CONNECT TO 'MS Access ODBC 2.0';
//EXIT Script;
//vTodayMinusXMonths= date(addmonths(today(),-36),'YYYYMMDD')
//Load SubField(ProductName, ' ',2) As Department
//From "C:\WINDOWS\QWT.mdb"."Products";
//Customers:
//Load SubField(ProductName, ' ',2) As Department
//From "C:\Users\shimo\OneDrive\Documents\QWT.mdb"."Products";
REM LOAD CustomerID,
CompanyName,
ContactName,
Address,
City,
StateProvince,
PostalCode,
Country,
Phone,
Fax;
SQL SELECT CustomerID,
CompanyName,
ContactName,
Address,
City,
StateProvince,
PostalCode,
Country,
Phone,
Fax
FROM "C:\WINDOWS\QWT.mdb"."Customers";
Loosen Table Customers;
REM LOAD OrderID,
ProductID,
UnitPrice,
Quantity,
Discount;
SQL SELECT OrderID,
ProductID,
UnitPrice,
Quantity,
Discount
FROM "C:\WINDOWS\QWT.mdb"."Order Details";
//Loosen Table Order_Details;
//Let vCurrentMonth= Max(OrderDate);
//Let vdate = Addmonths(today(), -36,'MMM-YYYY');
//Date(MonthStart(OrderDate),'MMM-YY') as Month;
//Let formatmonth = monthname(OrderDate, -1);
REM LOAD OrderID,
CustomerID,
EmployeeID,
Freight,
OrderDate,
ShipperID;
SQL SELECT OrderID,
CustomerID,
EmployeeID,
Freight,
OrderDate,
ShipperID
FROM "C:\WINDOWS\QWT.mdb"."Orders"
WHERE OrderDate >= $(vdate);
Loosen Table Orders;
REM LOAD ProductID,
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitCost,
UnitPrice,
UnitsInStock,
UnitsOnOrder;
SQL SELECT ProductID,
ProductName,
SupplierID,
CategoryID,
QuantityPerUnit,
UnitCost,
UnitPrice,
UnitsInStock,
UnitsOnOrder
FROM "C:\WINDOWS\QWT.mdb"."Products";
Loosen Table Products;
LOAD
EmpID,
"Last Name",
"First Name",
Title,
"Hire Date",
Office,
"Extension",
"Reports To",
"Year Salary",
"First Name"&' '&"Last Name" as full_name
FROM [lib://C/EmpOff.xls]
(biff, embedded labels, table is Employee$);
MapEmpIDtofull_name:
Mapping LOAD
EmpID,
"First Name"&' '&"Last Name" as full_name
FROM [lib://C/EmpOff.xls]
(biff, embedded labels, table is Employee$);
LIB CONNECT TO 'MS Access ODBC 2.0';
Orders:
LOAD *,
ApplyMap('MapEmpIDtofull_name', EmployeeID, null()) as Sales_Rep;
SQL Select *
From "C:\WINDOWS\QWT.mdb"."Orders";
LET vFolderPath = 'lib://MS Access ODBC 2.0';
Load SubField(ProductName, ' ',2) As Department;
SQL Select *
From "C:\WINDOWS\QWT.mdb"."Products";
LatestDate:
LOAD
max([OrderDate]) as maxDate,
min([OrderDate]) as minDate;
SQL Select *
From "C:\WINDOWS\QWT.mdb"."Orders";
LET vCurrentDate = Date(Peek('maxDate',0,'LatestDate'),'MM/DD/YYYY');
LET vMinDate = Date(Peek('minDate',0,'LatestDate'),'MM/DD/YYYY');
Drop Table LatestDate;