Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date issue while extracting data .

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;

1 Solution

Accepted Solutions
Peter_Cammaert
Partner
Partner

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

View solution in original post

16 Replies
santiago_respane
Specialist
Specialist

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,

sunny_talwar

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');

Not applicable
Author

Untitled.jpgDear 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 .

trdandamudi
Master II
Master II

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');


trdandamudi
Master II
Master II

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;


Not applicable
Author

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 .

trdandamudi
Master II
Master II

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');

Not applicable
Author

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 .

sunny_talwar

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