topic Re: Difference from 1st and 2nd timestamps within the same column in QlikView

Difference from 1st and 2nd timestamps within the same column

xarapre7 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All!

How can i get the difference of the last timestamp and 2nd to the last timestamp within the same column (TimeStamp1 column) for the same CaseNo? And if a case has no second record (eg Case 789 below), it should get the difference of TimeStamp2 - TimeStamp1?

Case TimeStamp1 TimeStamp2 Calculation Expected Result

123 7/1/2016 10:30PM 7/1/2016 11:30PM =7/1/2016 10:30PM - 7/1/2016 10:30AM 12:00:00 HRS

123 7/1/2016 10:30AM 7/1/2016 11:30AM

123 6/11/2016 10:00PM 6/11/2016 11:00PM

456 4/2/2016 10:30AM 4/2/2016 11:30AM =4/2/2016 10:30AM - 4/1/2016 10:30PM 24:00:00 HRS

456 4/1/2016 10:30AM 4/1/2016 11:30AM

789 1/5/2016 9:20AM 1/7/2016 9:20AM =1/7/2016 9:20AM - 1/5/2016 9:20AM 48:00:00 HRS

Thank you in advance for your help.

Re: Difference from 1st and 2nd timestamps within the same column

sunny_talwar — Wed, 12 Jul 2017 22:52:19 GMT

May be use Peek/Previous here

Peek() vs Previous() – When to Use Each

PEEK() VS PREVIOUS() – Qlik school‌

Peek() or Previous() ?

Re: Difference from 1st and 2nd timestamps within the same column

xarapre7 — Thu, 13 Jul 2017 09:11:47 GMT

I'm still not getting my expected result from below script:

TEST:

LOAD

floor(LastMod-Change)AS DayDiff,

time(frac(Change) - frac(LastMod), 'hh:mm:ss') as TimeDiff,

if(Case=Previous(Case),

//timestamp(Previous(time(frac(Change)-frac(Change))),'hh:mm:ss')),

floor(Previous(Change)-(Change)))&' '& time(frac(Change) - frac(Change),'hh:mm:ss') ,

floor(LastMod-Change)&' '& time(frac(Change) - frac(LastMod), 'hh:mm:ss') as PreviousChange,

* INLINE [

Case, Change, LastMod

123, 8/1/2016 10:24:00 AM, 8/5/2016 10:24:00 PM

123, 7/1/2016 12:24:00 AM, 7/5/2016 7:24:00 PM

123, 6/1/2016 12:24:00 AM, 6/8/2016 6:24:00 AM

456, 3/1/2016 10:24:00 AM, 3/5/2016 5:24:00 PM

456, 2/1/2016 12:24:00 AM, 2/5/2016 4:24:00 AM

789, 1/1/2016 12:24:00 AM, 1/3/2016 3:24:00 AM

]

;

The highlighted cells below should get the difference between 1st and previous rows from Change column which has the same case no. Also, I want the results all in time instead of day and time. Please help. Thanks.

Re: Difference from 1st and 2nd timestamps within the same column

sunny_talwar — Thu, 13 Jul 2017 11:12:00 GMT

I think I am a little lost.... Can you please elaborate as to what the expected output needs to look like?

Re: Difference from 1st and 2nd timestamps within the same column

xarapre7 — Thu, 13 Jul 2017 16:01:58 GMT

So for the 1st row under PreviousChange column (highlighted), that should be the difference between 8/1/2016 and 7/1/2016. which should be like 30 days and 10 hrs = 730 hrs (approximately).

720 hrs (getting the diff between 7/1/2016 and 6/1/2016)

186 hrs (since this one doesn't have a previous ChangeDate for 123, then get the difference of Change-LastMod) = 186 hrs (= 7 days and 18 hrs -3rd row output from PreviousChange)

Re: Difference from 1st and 2nd timestamps within the same column

antoniotiman — Thu, 13 Jul 2017 16:40:05 GMT

Hi Preciosa,

try like this

Temp:
LOAD * INLINE [
Case, Change, LastMod
123, 8/1/2016 10:24:00 AM, 8/5/2016 10:24:00 PM
123, 7/1/2016 12:24:00 AM, 7/5/2016 7:24:00 PM
123, 6/1/2016 12:24:00 AM, 6/8/2016 6:24:00 AM
456, 3/1/2016 10:24:00 AM, 3/5/2016 5:24:00 PM
456, 2/1/2016 12:24:00 AM, 2/5/2016 4:24:00 AM
789, 1/1/2016 12:24:00 AM, 1/3/2016 3:24:00 AM
];
LOAD *,
If(Case=Peek(Case),Interval(RangeSum(Change,-Peek(Change)),'dd:hh:mm'),Interval(LastMod-Change,'dd:hh:mm')) as PreviousChange
Resident Temp
Order By Case,Change;
Drop Table Temp;

Regards,

Antonio

Re: Difference from 1st and 2nd timestamps within the same column

xarapre7 — Thu, 13 Jul 2017 21:43:40 GMT

Perfect! Thank you Antonio! Your above script resolved my problem.