Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
March 11, 2PM EST: Do More with Qlik - Getting Started wtih Qlik Sense SaaS - Redux. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai33
Partner
Partner

Where Clause not working only for a particular field on Select statement

Hello Everyone,

I'm facing a very strange scenario. I have a table with 5 fields partitiontimestamp, [year], [month], [day], [hour]

When i try to load the data with a Where condition on [year], [month], [day], [hour] it loads fine and generates the expected data.

But, when i try to load data with filter on partitiontimestamp it says 'Field Not Found'. Actually the filed is existing on the Source table. 

Please see below snapshots for a better understanding

Runs successfully when other fields are used are filtersRuns successfully when other fields are used are filtersData Model Viewer after successful loadData Model Viewer after successful loadError when partitiontimestamp has been used in FilterError when partitiontimestamp has been used in Filterpartitiontimestamp used as filterpartitiontimestamp used as filter

Labels (1)
1 Solution

Accepted Solutions
Sai33
Partner
Partner

Thanks everyone for your valuable inputs.

My Datasource here was a Hive DataLake and the User which was fetching data from there was not having enough rights to execute the exact where condition i was looking for (But, was able to execute the other Where condition 😲)

So, i had to add both these setting to the Qlik - Hive connection 

SET hive.execution.engine = tez;

SET tez.queue.name=pr-*;

Then i was able to run my script successfully 😥

 

 

 

View solution in original post

11 Replies
TimvB
Creator II
Creator II

You probably need to use straight brackets for the field partitiontimestamp in the where clause .

 

 

Sai33
Partner
Partner

Hi Tim,

 

Have tried all possible things (partitiontimestamp)  [partitiontimestamp] also 'partitiontimestamp'

But, it somehow doesn't work

TimvB
Creator II
Creator II

What data type is partitiontimestamp in the database?

Sai33
Partner
Partner

Text

Sai33
Partner
Partner

Also the other 4 fields are of the same DataType

TimvB
Creator II
Creator II

Have you tried deleting the "comment" within the SQL statement?

Sai33
Partner
Partner

Sorry i didn't get you.

This is my statement : 

LIB CONNECT TO 'Apache_Hive_CNBDP_EMEA_INTAUTO';

LOAD
partitiontimestamp,
year,
month,
day,
hour
;

[e_condis_derived_location]:
SELECT
partitiontimestamp,
[year],
[month],
[day],
[hour]
FROM [condis_source].[e_condis_derived_location]
Where partitiontimestamp='2019091709'
// Where [year]='2019' and [month]='09' and [day]='17' and [hour]='09'
;

TimvB
Creator II
Creator II

I have two options left:

Option 1: Delete the comment of the other where clause in the SQL statement. Sometimes Qlik handels comments as script when it is in an SQL statement.

LIB CONNECT TO 'Apache_Hive_CNBDP_EMEA_INTAUTO';

LOAD
partitiontimestamp,
year,
month,
day,
hour
;

[e_condis_derived_location]:
SELECT
partitiontimestamp,
[year],
[month],
[day],
[hour]
FROM [condis_source].[e_condis_derived_location]
Where partitiontimestamp='2019091709'
;

 

Option 2: Instead of straight brackets, use straight quotation marks ("):

 

LIB CONNECT TO 'Apache_Hive_CNBDP_EMEA_INTAUTO';

LOAD
partitiontimestamp,
year,
month,
day,
hour
;

[e_condis_derived_location]:
SELECT
partitiontimestamp,
[year],
[month],
[day],
[hour]
FROM [condis_source].[e_condis_derived_location]
Where "partitiontimestamp"='2019091709'
;

Sai33
Partner
Partner

unfortunately no, both the options aren't working.

Thanks for your inputs though!