Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

shimon_klick
New Contributor

Unable to load data by filtering date

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

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

Tags (2)
11 Replies
mdmukramali
Valued Contributor II

Re: Unable to load data by filtering date

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
Contributor II

Re: Unable to load data by filtering date

Check date formats of both vdate and OrderDate ...

passionate
Valued Contributor

Re: Unable to load data by filtering date

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

WHERE OrderDate >= '$(vdate)';

sumit_ranjan_pa
Contributor

Re: Unable to load data by filtering date

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
Honored Contributor III

Re: Unable to load data by filtering date

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

shimon_klick
New Contributor

Re: Unable to load data by filtering date

Screenshot (50).png

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

Can you please help?

New to Qlik Sense

shimon_klick
New Contributor

Re: Unable to load data by filtering date

hi,

can you please look into it?

sasiparupudi1
Honored Contributor III

Re: Unable to load data by filtering date

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

shimon_klick
New Contributor

Re: Unable to load data by filtering date

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;

Community Browser