Date issue while extracting data .

[#1]

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;

[santiago_respane]

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

[#4]

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 .

[trdandamudi]

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]

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;

[#7]

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]

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

[#9]

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