Need Help..

[paulwalker]

Hi Community,

I have one flat file in that Startdate and End Date like this..

JOB|JOB NAME|ERROR|START DT/TIME|END DT/TIME|ERROR MESSAGE

PLSQL|TRDAS_ST_OWN.SP_TRD_SUBSCRIPNAME|05/14/2015 00:15:35|05/14/2015 00:16:00

PLSQL|TRDAS_ST_OWN.SP_TRD_SUBSCRIPNAME|05/14/2015 08:15:35|05/14/2015 08:16:00

How to calculate difference in 2 dates...  (like Enddate - StartDate )

PFA,

Thanks in Advance...

[#2]

Difference in Days ???

[buzzy996]

follow tis,

https://community.qlik.com/message/611417#611417

else

Today() - Date(DueDate) AS DateDiff;

[sunny_talwar]

Try this:

Table:

LOAD *,

  Interval(EndDate - StartDate, 'hh:mm:ss') as Difference;

LOAD *,

  Date#(SubField(Field, '|', 3), 'MM/DD/YYYY hh:mm:ss') as StartDate,

  Date#(SubField(Field, '|', 4), 'MM/DD/YYYY hh:mm:ss') as EndDate;

LOAD [JOB|JOB NAME|ERROR|START DT/TIME|END DT/TIME|ERROR MESSAGE] as Field

FROM

TestData.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

[paulwalker]

HI ,

PFA,

direct i have extract from text file.. but same thing its not working here..

Please help me out..

[maxgro · Accepted solution]

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

LOAD @1 as JOB,

     @2 as [JOB NAME],

     null() as ERROR,

     @3 as [START DT/TIME],

     @4 as [END DT/TIME],

     @5 as [ERROR MESSAGE],

     interval(@4 - @3, 'hm:mm:ss') as [DATETIME DIFF]

FROM

TestData.txt

(txt, codepage is 1252, no labels, delimiter is '|', msq)

where RecNo() > 1

;

[sunny_talwar]

Data load is incorrect. Error is loading in the end date and start is loading the end date.

In addition QV doesn't understand your timestamp, you will have to force it to understand it either using TimeStamp# or Date# functions.

HTH

Best,

Sunny

[paulwalker]

Thanks EveryOne

[sunny_talwar]

If you got your solution, then you should mark a correct answer to close this thread. I think in your case the best solution has been provided by Massimo Grossi‌.

Best,

Sunny