topic Re: Date issue while extracting data . in QlikView

Date issue while extracting data .

Wed, 25 Nov 2020 16:16:04 GMT

Dear Experts ,

I am trying to extract data from below table . when i remove date filter from where condition its working fine , where when i add date filter its displaying zero records found .

when i execute same query in back end (sql tool) i am getting 1 million record .

Please suggest, i am doing anything wrong in adding date filter , else data format is wrong in my condition .

TransactionFact :

SQL SELECT did,document_date from fas_gl_transaction where company_code = 'TEST' and trunc(document_date) between '01-APR-2013' and '31-MAR-2014';

STORE TransactionFact into ..\QVD\TEST\TransactionFact_History_2013.QVD;

Re: Date issue while extracting data .

santiago_respane — Fri, 29 Apr 2016 17:28:26 GMT

Hi Sriram,

your query looks fine to me.

Maybe you can try comparing the values in your query ('01-APR-2013') with the result of the trunc(document_date) and see if their format matches. I dont see any other possible issue in your query, also company 'TEST' should exists.

Please let us know if you need anything else or if you found a solution for your issue.

Kind regards,

Re: Date issue while extracting data .

sunny_talwar — Fri, 29 Apr 2016 22:46:32 GMT

Not really sure if this would anything, but try to put parenthesis around your between logic

TransactionFact :

SQL SELECT

did,

document_date

FROM fas_gl_transaction

Where (company_code = 'TEST') and

(trunc(document_date) between '01-APR-2013' and '31-MAR-2014');

Re: Date issue while extracting data .

Sat, 30 Apr 2016 06:24:54 GMT

Dear All ,

Thanks for your quick reply , Kindly check my attached image . i extracted qvd without date filter and placed document date column in table box , where i am getting record for the year 2013 and 2014 .when i add date filter its not fetching any record . i tried with load statement as per settu_periasamy still getting same issue .

kinldy suggest .

Re: Date issue while extracting data .

trdandamudi — Sat, 30 Apr 2016 09:04:26 GMT

Can you try !the below:

TransactionFact :

SQL SELECT

did,

document_date

FROM fas_gl_transaction

Where company_code = 'TEST' and

Trim(Trunc(document_date) between '01-APR-2013' and '31-MAR-2014');

Re: Date issue while extracting data .

trdandamudi — Sat, 30 Apr 2016 12:12:37 GMT

Can you please try the below also and I am hoping this should work:

TransactionFact :

Load

did,

document_date,

Where company_code = 'TEST' and

Trim(document_date between '01-APR-2013' and '31-MAR-2014');

SQL SELECT

did,

document_date,

company_code,

FROM fas_gl_transaction;

Re: Date issue while extracting data .

Sun, 01 May 2016 14:32:56 GMT

Dear Thiru ,

I have tried the same ,but i am getting error (error in expression ')' expeccted ) , when i click OK button its fetching record . Pls check my attched notepad file and suggest .

Re: Date issue while extracting data .

trdandamudi — Sun, 01 May 2016 14:51:24 GMT

There is no IN function in Qlikview, so do the following:

Replace this code: and company_code IN ('FSL','RML','NASS','CML');

With this: and Match(company_code,'FSL','RML','NASS','CML');

Re: Date issue while extracting data .

Mon, 02 May 2016 07:12:34 GMT

Dear Thiru ,

Instead of 'IN' , I used match in load statement , but i am getting same error .

So i put company filter in select statement and date filter in Load . still getting same error(error in expression ')' expected )

Please check attached files .

Re: Date issue while extracting data .

sunny_talwar — Mon, 02 May 2016 07:16:09 GMT

Because you cannot use between in QlikView. QlikView doesn't support between function. Plus, I won't even recommend using the where statement in the Load statement. I would filter out things in your sql statement so that the query has to bring less number of records to QlikView.

I would try to check if the query runs in SQL and try to resolve the issue there and then try to run with the fixed SQL query

Re: Date issue while extracting data .

Peter_Cammaert — Mon, 02 May 2016 08:26:36 GMT

In his OP he said:

when i execute same query in back end (sql tool) i am getting 1 million record

which makes it a very weird issue because the execution is done in both cases by the database engine. Could it be permission-related? Seems improbable...

Sriram, try rewriting or removing parts of the WHERE condition in your SELECT statement. For example, it could be that the database doesn't understand the between date values anymore in your QlikView script context. Can you rewrite them as default database dates? Like

WHERE trunc(document_date) between '2013-04-01' and '2014-03-31'

Best,

Peter

Re: Date issue while extracting data .

Mon, 02 May 2016 09:05:53 GMT

Dear Peter ,

Thanks a lot , Now its fetching data after changing date format as per your reply . i am using oracle oledb connection to extract record , where i never thought this date format will create such a issue . could you please tell me whats a difference between using date format in (DD-MON-YYYY) and (YYYY-MON-DD)

Re: Date issue while extracting data .

Peter_Cammaert — Mon, 02 May 2016 09:15:31 GMT

The standard database date format isn't YYYY-MON-DD, it rather is YYYY-MM-YY meaning that all fields are numeric.

There isn't much of a difference except that your format may not work for various reasons (language/regional settings is one that comes to mind - month names are different in different languages), while the database date format YYYY-MM-YY will always work. Especially when working with Oracle.

Best,

Peter

Re: Date issue while extracting data .

sunny_talwar — Mon, 02 May 2016 09:18:05 GMT

Peter not really sure how all this work. But when we have to restrict dates while grabbing stuff from Oracle, we use DD-MMM-YYYY

UPDATE: I guess the only difference is the casing. I would have used this:

Trim(document_date) between '01-Apr-2013' and '31-Mar-2014';

vs.

Trim(document_date) between '01-APR-2013' and '31-MAR-2014';

Not sure if that make any difference.

Re: Date issue while extracting data .

Peter_Cammaert — Mon, 02 May 2016 09:21:23 GMT

Sunny, feel free to use whatever works best for you.

Re: Date issue while extracting data .

sunny_talwar — Mon, 02 May 2016 09:24:36 GMT

Peter -

I am sorry if you found my response to be offensive. But it was more of a question for you. I will have to use what has been told by the SQL guys. But do you know why is it working with MMM in my case? If you don't know, then that is fine

Best,

Sunny

Re: Date issue while extracting data .

Peter_Cammaert — Mon, 02 May 2016 10:29:50 GMT

Nah, not offensive at all. Of course not.

I don't know why short month names are working for you. And whatever I say, the short month names appear not to work in Sriram's QlikView context. In my experience, standard database dates work best and consistently across DBMS products. I admit I haven't tested them all.

So if it works for you, go ahead and use those date formats. Especially when the SQL gurus have a final say in what you should and what you shouldn't use. That's at least one thing less to worry about

Take care,

Peter.