Re: Unable to load data by filtering date - Page 2 - Qlik Community

Anonymous · ‎2018-04-15

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

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

Anonymous · ‎2018-04-16

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.

passionate · ‎2018-04-17

I think your variable has no value

Please check variable value