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

[xarapre7]

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.

[sunny_talwar]

May be use Peek/Previous here

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

PEEK() VS PREVIOUS() – Qlik school‌

Peek() or Previous() ?

[xarapre7]

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.

[sunny_talwar]

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

[xarapre7]

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)

[antoniotiman]

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

[xarapre7]

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