CODEWITHSUNDEEP
datewhere-clauseqlikviewqliksense
Guillaume Vaillant
Guillaume Vaillant

Reputation: 1

Clause where doesnt work qliksense

I have a problem with qliksense. I cant charge my date with request sql oracle (more than million lines). I believe that 'where clause' with my DATE doesn't work. I want filter !

Can you help me to resolve this proble ?

2 case : * No data charging * Charging all lines

My hypothesis: * data is corrupt * bad parameter oracle / qlik sense

My oracle parameter :

NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR

Parameter Qliksense :

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY'; /* DD/MM/YYYY hh:mm:ss[.fff] */

The script, DTTINS is type DATE :

MATABLE:
Select 
IDTCTR
from dwh.matable 
where DTTINS >= '01/01/2016' ;

store MATABLE into $(qvdfait)matable.qvd (qvd);

This script work in sql/developper I have try everithing put DTTINS with to_char, to_number, to_date, change the Date format

Thx for your help

Upvotes: 0

Views: 580

Answers (1)

EldadT
EldadT

Reputation: 932

oracle dateformat is different from Qliks'. for example month in oracle is "MON" and not "MM". In the load you have to specify the exact format you want to compare;

Use: to_char(DTTINS,'DD/MON/YYYY') >= '01/JAN/2016' ;

Upvotes: 1

Related Questions