Qliksense: Select where and Like leads to field not found..

[wuming79]

Hi,

I have a database where I want to select before load. The SQL command worked in SQL but it doesn't seem to work in Qliksense. I have tried replacing LIKE with AND and it didn't work too. 

Select * from myTable where field1 = 'customerA' LIKE date = '2018-12-06%';

When I load customer as Customer_List;

Qliksense reports error as:

Field 'customer' not found. 

When I use the same syntax on another table with similar fields, it works.  

What could be the potential issue? How can I troubleshoot this issue?

My table has 47430974 rows. I'm not sure if there is a limitation to select from a table with 47430974 rows?

[marcus_sommer]

Are you really sure that your syntax is valid within your database and in regard to the queried table? If no, just correct it and if yes you need to check/replace the used database-driver because Qlik doesn't execute any SQL itself else it just transferred the statement and get the results (and the number of records is irrelevant unless in regard that it might need some time for the database and the used network to transfer the data and you might run into any timeout or it breaks because of not enough RAM or similar issues - but in such case you would get another error-message).

- Marcus

[Mauritz_SA · Accepted solution]

Hi there

I am no SQL expert, but looking at your syntax I would say that the 

field1 = 'customerA' LIKE date = '2018-12-06%'

should rather be something like

field1 = 'customerA' AND date LIKE '2018-12-06%'

However, if your date is stored as a date I would rather use the DATEPART function to do it it like this:

SELECT
*
FROM
myTable
WHERE
field1 = 'customerA'
AND DATEPART(yy,date) = 2018
AND DATEPART(mm,date) = 12
AND DATEPART(dd,date) = 06;

I tested this in SQL and Qlik Sense and it worked on my DB.

Good luck!

Regards,

Mauritz

[wuming79]

Hi Mauritz_SA,

Thanks. 

[wuming79]

Hi Marcus, 

I did encounter another message but decided to close this thread since it's another topic.  My new topic is at https://community.qlik.com/t5/New-to-Qlik-Sense/Qliksense-SQL-limits-to-Select-number-of-records/m-p.... Hope you help to take a look at it. Thanks in advance.