topic Re: Need Help.. in QlikView

Need Help..

paulwalker — Fri, 15 May 2015 15:52:03 GMT

Hi Community,

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

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

Re: Need Help..

Fri, 15 May 2015 16:03:22 GMT

Difference in Days ???

Re: Need Help..

buzzy996 — Fri, 15 May 2015 16:05:04 GMT

follow tis,

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

else

Today() - Date(DueDate) AS DateDiff;

Re: Need Help..

sunny_talwar — Fri, 15 May 2015 16:05:19 GMT

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;

FROM

TestData.txt

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

Re: Need Help..

paulwalker — Fri, 15 May 2015 16:20:09 GMT

HI ,

PFA,

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

Please help me out..

Re: Need Help..

maxgro — Fri, 15 May 2015 16:21:18 GMT

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

;

Re: Need Help..

sunny_talwar — Fri, 15 May 2015 16:24:41 GMT

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

Re: Need Help..

paulwalker — Fri, 15 May 2015 16:27:16 GMT

Thanks EveryOne

Re: Need Help..

sunny_talwar — Fri, 15 May 2015 16:32:38 GMT

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