11 Replies Latest reply: Apr 17, 2018 10:42 AM by pankaj thakur RSS

    Unable to load data by filtering date

    shimon Gowda

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

       

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

        • Re: Unable to load data by filtering date
          Mohammed Mukram Ali

          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

          • Re: Unable to load data by filtering date
            shekar shekhar

            Check date formats of both vdate and OrderDate ...

            • Re: Unable to load data by filtering date
              pankaj thakur

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

               

              WHERE OrderDate >= '$(vdate)';

              • Re: Unable to load data by filtering date
                Sumit Panda

                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.

                • Re: Unable to load data by filtering date
                  Sasidhar Parupudi

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

                    • Re: Unable to load data by filtering date
                      shimon Gowda

                      hi,

                       

                      can you please look into it?

                        • Re: Unable to load data by filtering date
                          Sasidhar Parupudi

                          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

                            • Re: Unable to load data by filtering date
                              shimon Gowda

                              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;

                              • Re: Unable to load data by filtering date
                                shimon Gowda

                                Hi,

                                 

                                Im getting an error when i add the ' ' to the Where Statement,

                                WHERE OrderDate >= '$(vdate)';


                                PFB log!

                                2018-04-16 10:58:35      Execution started.

                                2018-04-16 10:58:35      Qlik Sense Server Version     12.108.6

                                2018-04-16 10:58:35      CPU Target                    x64

                                2018-04-16 10:58:35      Operating System              Windows 10 Home  (64 bit edition)

                                2018-04-16 10:58:35      Wow64 mode                    Not using Wow64

                                2018-04-16 10:58:35      Language                      0409 English English

                                2018-04-16 10:58:35      Country                       USA United States United States

                                2018-04-16 10:58:35      MDAC Version                  6.3.9600.16384

                                2018-04-16 10:58:35      MDAC Full Install Version     6.3.9600.16384

                                2018-04-16 10:58:35      PreferredCompression          2

                                2018-04-16 10:58:35      EnableParallelReload          1

                                2018-04-16 10:58:35      ParallelizeQvdLoads           1

                                2018-04-16 10:58:35      EnableFlushLog                0

                                2018-04-16 10:58:35      UserLogfileCharset            65001

                                2018-04-16 10:58:35      OdbcLoginTimeout              -1

                                2018-04-16 10:58:35      OdbcConnectionTimeout         -1

                                2018-04-16 10:58:35      LongestPossibleLine           1048576

                                2018-04-16 10:58:35      ScriptWantsDbWrite            false

                                2018-04-16 10:58:35      ScriptWantsExe                false

                                2018-04-16 10:58:35      ICU_FOR_CHARACTER_CLASSIFICATION true

                                2018-04-16 10:58:35      ICU_FOR_CODE_PAGES            true

                                2018-04-16 10:58:35      ICU_FOR_COLLATION             true

                                2018-04-16 10:58:35      ICU_FOR_LOCALE_INFO           true

                                2018-04-16 10:58:35      ICU_FOR_SUPPORT_INFO          true

                                2018-04-16 10:58:35      ICU_FOR_TIME_ZONE_INFO        true

                                2018-04-16 10:58:35      LogFile CodePage Used:        65001

                                2018-04-16 10:58:35      UseMemoryFillPattern          1

                                2018-04-16 10:58:35      ScriptMode                    Standard (limits system access)

                                2018-04-16 10:58:35      SequentialThreadCount         14

                                2018-04-16 10:58:35      NumberOfCores (effective)     4

                                2018-04-16 10:58:35      ReloadCodebase                Bnf

                                2018-04-16 10:58:35      Reload Executed By            Personal\Me

                                2018-04-16 10:58:35      Process Executing             Qlik Sense Server

                                2018-04-16 10:58:35      Process ID                    9220

                                2018-04-16 10:58:35      Grpc version                  3.0.0

                                2018-04-16 10:58:35      Protobuf version              3.2.0

                                2018-04-16 10:58:35 0002 SET ThousandSep=','

                                2018-04-16 10:58:35 0003 SET DecimalSep='.'

                                2018-04-16 10:58:35 0004 SET MoneyThousandSep=','

                                2018-04-16 10:58:35 0005 SET MoneyDecimalSep='.'

                                2018-04-16 10:58:35 0006 SET MoneyFormat='$#,##0.00;-$#,##0.00'

                                2018-04-16 10:58:35 0007 SET TimeFormat='h:mm:ss TT'

                                2018-04-16 10:58:35 0008 SET DateFormat='M/D/YYYY'

                                2018-04-16 10:58:35 0009 SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'

                                2018-04-16 10:58:35 0010 SET FirstWeekDay=6

                                2018-04-16 10:58:35 0011 SET BrokenWeeks=1

                                2018-04-16 10:58:35 0012 SET ReferenceDay=0

                                2018-04-16 10:58:35 0013 SET FirstMonthOfYear=1

                                2018-04-16 10:58:35 0014 SET CollationLocale='en-US'

                                2018-04-16 10:58:35 0015 SET CreateSearchIndexOnReload=1

                                2018-04-16 10:58:35 0016 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

                                2018-04-16 10:58:35 0017 SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December'

                                2018-04-16 10:58:35 0018 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

                                2018-04-16 10:58:35 0019 SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday'

                                2018-04-16 10:58:35 0022 Let vCurrentMonth = num(month(vCurrentDate))

                                2018-04-16 10:58:35 0023 Let vdate = Addmonths(vCurrentDate, -(+35),'MMM-YYYY')

                                2018-04-16 10:58:35 0028 LIB CONNECT TO 'MS Access ODBC 2.0'

                                2018-04-16 10:58:37 0040 REM *** ******

                                2018-04-16 10:58:37 0041     ******

                                2018-04-16 10:58:37 0042     ******

                                2018-04-16 10:58:37 0043     ******

                                2018-04-16 10:58:37 0044     ******

                                2018-04-16 10:58:37 0045     ******

                                2018-04-16 10:58:37 0046     ******

                                2018-04-16 10:58:37 0047     ******

                                2018-04-16 10:58:37 0048     ******

                                2018-04-16 10:58:37 0049     ***

                                2018-04-16 10:58:37 0052 SQL SELECT CustomerID,

                                2018-04-16 10:58:37 0053     CompanyName,

                                2018-04-16 10:58:37 0054     ContactName,

                                2018-04-16 10:58:37 0055     Address,

                                2018-04-16 10:58:37 0056     City,

                                2018-04-16 10:58:37 0057     StateProvince,

                                2018-04-16 10:58:37 0058     PostalCode,

                                2018-04-16 10:58:37 0059     Country,

                                2018-04-16 10:58:37 0060     Phone,

                                2018-04-16 10:58:37 0061     Fax

                                2018-04-16 10:58:37 0062 FROM "C:\WINDOWS\QWT.mdb"."Customers"

                                2018-04-16 10:58:37      10 fields found: CustomerID, CompanyName, ContactName, Address, City, StateProvince, PostalCode, Country, Phone, Fax,

                                2018-04-16 10:58:37      92 lines fetched

                                2018-04-16 10:58:37 0063 Loosen Table Customers

                                2018-04-16 10:58:37 0066 REM *** ******

                                2018-04-16 10:58:37 0067     ******

                                2018-04-16 10:58:37 0068     ******

                                2018-04-16 10:58:37 0069     ******

                                2018-04-16 10:58:37 0070     ***

                                2018-04-16 10:58:37 0071 SQL SELECT OrderID,

                                2018-04-16 10:58:37 0072     ProductID,

                                2018-04-16 10:58:37 0073     UnitPrice,

                                2018-04-16 10:58:37 0074     Quantity,

                                2018-04-16 10:58:37 0075     Discount

                                2018-04-16 10:58:37 0076 FROM "C:\WINDOWS\QWT.mdb"."Order Details"

                                2018-04-16 10:58:37      5 fields found: OrderID, ProductID, UnitPrice, Quantity, Discount,

                                2018-04-16 10:58:37      2,172 lines fetched

                                2018-04-16 10:58:37 0083 REM *** ******

                                2018-04-16 10:58:37 0084     ******

                                2018-04-16 10:58:37 0085     ******

                                2018-04-16 10:58:37 0086     ******

                                2018-04-16 10:58:37 0087     ******

                                2018-04-16 10:58:37 0088     ***

                                2018-04-16 10:58:37 0089 SQL SELECT OrderID,

                                2018-04-16 10:58:37 0090     CustomerID,

                                2018-04-16 10:58:37 0091     EmployeeID,

                                2018-04-16 10:58:37 0092     Freight,

                                2018-04-16 10:58:37 0093     OrderDate,

                                2018-04-16 10:58:37 0094     ShipperID

                                2018-04-16 10:58:37 0095 FROM "C:\WINDOWS\QWT.mdb"."Orders"

                                2018-04-16 10:58:37 0096 WHERE OrderDate >=

                                2018-04-16 10:58:37      Error: Connector reply error: SQL##f - SqlState: 37000, ErrorCode: 4294964196, ErrorMsg: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'OrderDate >='.

                                2018-04-16 10:58:37      Execution Failed

                                2018-04-16 10:58:37      Execution finished.Screenshot (34).png