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

[Sai33]

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 filtersData Model Viewer after successful loadError when partitiontimestamp has been used in Filterpartitiontimestamp used as filter

[TimvB]

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

[Sai33]

Hi Tim,

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

But, it somehow doesn't work

[TimvB]

What data type is partitiontimestamp in the database?

[Sai33]

Text

[Sai33]

Also the other 4 fields are of the same DataType

[TimvB]

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

[Sai33]

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]

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]

unfortunately no, both the options aren't working.

Thanks for your inputs though!