topic Finding difference between two dates in QlikView

Finding difference between two dates

Wed, 21 Dec 2011 12:31:17 GMT

Dear Guru's

In the script Select Statement how we will find diffence with currdate() -datefield as DiffDate.

example: CurrDate()-DueDate as DiffDate.

Please help me...

Regards

Joe

Finding difference between two dates

SunilChauhan — Wed, 21 Dec 2011 12:33:40 GMT

use below code

today()-datefield as Diffdate

Finding difference between two dates

vijay_iitkgp — Wed, 21 Dec 2011 12:33:54 GMT

Hi Joe,

You can create a variable vcCurrentDate=Date(Today());

and use this variable in script

$(vCurrentDate)-DueDate as DiffDate.

Regards

Vijay

Finding difference between two dates

Wed, 21 Dec 2011 12:37:46 GMT

Dear Sunil,

Thanks for your reply

when I try the expression, I am getting the following error.

SQL Error:[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token [ was not valid. Valid tokens: ( + - ? : DAY INF NAN RID ROW RRN CASE CAST CHAR DATE DAYS HASH.

SQL Scriptline:

SQL State:37000

Regards

Joe

Finding difference between two dates

SunilChauhan — Wed, 21 Dec 2011 12:43:08 GMT

you can use if you using sql as backend

GetDate()- datefield as diffdate

Re: Finding difference between two dates

Miguel_Angel_Baeyens — Wed, 21 Dec 2011 12:52:27 GMT

Joe,

Use a LOAD statement. It has been discussed before how useful is this to control which data you put into QlikView instead of controlling with SQL statements which data you pull from your data source:

Table:
LOAD Today() - Date(DueDate) AS DateDiff;
SQL SELECT DueDate 
FROM Data Source;

Hope that helps.

Miguel

Finding difference between two dates

Wed, 21 Dec 2011 12:54:54 GMT

Dear Sunil,

the same error coming again, any workaround

I tried DateDiff(Currdate(),Duedate) as DiffDate no result

Regards

Joe

Finding difference between two dates

Wed, 21 Dec 2011 13:08:20 GMT

Dear Miguel,

I am trying in the following select statement, the same error appears.

Please suggest solution for the below.

ODBC CONNECT TO AS400 (XUserId is NMTLEB, XPassword is NMTTPB);

Trend:

Load

CORP,

SUB,

LOC,

PRON,

RUPEE,

Today()-Date(Duedate) as DateDiff;

SQL SELECT

CORP as Client,

SUB as Subsidary,

LOC as Location,

Count(PRON) as Invcount,

Sum(RUPEE) as Amount,

DateDiff

FROM S104F31D.Data

Where CORP =180

Group BY

CORP,SUB,LOC

;

Store Trend into Test.qvd;

Regards

Joe

Finding difference between two dates

vijay_iitkgp — Wed, 21 Dec 2011 13:23:40 GMT

Hi,

You need to extract DueDate instead of DateDiff in Sql Select Statement:

See Below:

Trend:

Load

CORP,

SUB,

LOC,

PRON,

RUPEE,

Today()-Date(Duedate) as DateDiff;

SQL SELECT

CORP as Client,

SUB as Subsidary,

LOC as Location,

Count(PRON) as Invcount,

Sum(RUPEE) as Amount,

DueDate

FROM S104F31D.Data

Where CORP =180

Group BY

CORP,SUB,LOC,DueDate

;

Store Trend into Test.qvd;

Finding difference between two dates

SunilChauhan — Wed, 21 Dec 2011 13:32:10 GMT

use below

DATEDIFF(day,getdate(),Datefield) AS DiffDate

ex:

DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate

hope this help

Finding difference between two dates

Thu, 22 Dec 2011 10:33:23 GMT

Dear Vijay,

As advised by you the script is working fine, still I have a problem in the Due Date, as I need the data of DATEDIFF because in the QVD I would like to store the differnce of today and duedate.

Any Workaround....

Trend:

Load

CORP,

SUB,

LOC,

PRON,

Rupee,

Today()-DATE(DueDate) AS DATEDIFF;

SQL SELECT

CORP,

SUB,

LOC,

Count(PRON) as Inv,

Sum(Rupee) as Amount,

Duedate

FROM S104F31D.REDFW6 Group by CORP,SUB,LOC,DueDate;

Store Trend into Test.qvd;

Finding difference between two dates

vijay_iitkgp — Thu, 22 Dec 2011 10:39:17 GMT

Try This:

Trend:

Load

CORP,

SUB,

LOC,

DATEDIFF,

Count(PORN) AS Invoice,

Sum(Rupee) AS AMount

Group By

CORP,

SUB,

LOC,

DATEDIFF;

Load

CORP,

SUB,

LOC,

PRON,

Rupee,

Today()-DATE(DueDate) AS DATEDIFF;

SQL SELECT

CORP,

SUB,

LOC,

PRON,

Rupee,

Duedate

FROM S104F31D.REDFW6 ;

Store Trend into Test.qvd;

Regards

Vijay

Finding difference between two dates

Thu, 22 Dec 2011 11:48:38 GMT

Dear Vijay,

Is it that am I missing anything the Load in between Load

First I am getting a errror as in the following and now I got the differnce of Due date but the Count(OHHPRON), Sum(OEURO) is not showing the count and sum.

Syntax error, missing/misplaced FROM:

Trend:

Load

OHHCORP,

OHHSUB,

OHHLOC,

OHHPRON,

OEURO,

DATEDIFF,

Count(OHHPRON) as Inv,

Sum(OEURO) as Eur,

Group By OHHCORP,OHHSUB,OHHLOC,DATEDIFF

Trend:

Load

OHHCORP,

OHHSUB,

OHHLOC,

OHHPRON,

OEURO,

DATEDIFF,

Count(OHHPRON) as Inv,

Sum(OEURO) as Eur,

Group By OHHCORP,OHHSUB,OHHLOC,DATEDIFF

This is the one I tried ....

Trend:

Load

OHHCORP,

OHHSUB,

OHHLOC,

OHHPRON,

OEURO,

DATEDIFF,

Count(OHHPRON) as Inv,

Sum(OEURO) as Eur,

Group By OHHCORP,OHHSUB,OHHLOC,DATEDIFF;

Load

OHHCORP,

OHHSUB,

OHHLOC,

OHHPRON,

OEURO,

Today()-DATE(ODUEDT) AS DATEDIFF;

SQL SELECT

OHHCORP,

OHHSUB,

OHHLOC,

OHHPRON,

OEURO,

ODUEDT

FROM S104F31D.EURDTA.REDFW6;

Store Trend into Test.qvd;

Regards

Joe

Finding difference between two dates

vijay_iitkgp — Thu, 22 Dec 2011 11:52:08 GMT

You are using comma after Sum(OEURO) AS Eur. Since it is end of statement so do not use coma here.

Regards

Vijay

Finding difference between two dates

Thu, 22 Dec 2011 12:22:07 GMT

Dear Vijay,

Thanks for your reply after removing the comma now I am getting a error of

ODBC read failed

SQL SELECT

OHHCORP,

OHHSUB,

OHHLOC,

OHHPRON,

OEURO,

ODUEDT

FROM S104F31D.EURDTA.REDFW6

I have put Join inbetween two Load statments, that is also not working.

Regards

Joe