Unable to load data by filtering date

[Anonymous]

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);

---------------------------------------------------------------------------------------------------------------

[mdmukramali]

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

[shekhar_analyti]

Check date formats of both vdate and OrderDate ...

[passionate]

Use Saingle Quotes in your where clause and make sure the format of two dates are same

WHERE OrderDate >= '$(vdate)';

[sumit_ranjan_pa]

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.

[sasiparupudi1]

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)';

[Anonymous]

I'm getting the following error, when i edit it to >='$(vdate)';

Can you please help?

New to Qlik Sense‌

[Anonymous]

hi,

can you please look into it?

[sasiparupudi1]

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

[Anonymous]

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;