How to compare the rows with in One Column and Substract the time stamp

[ravikumar_iyana]

Hi All,

I have to compare the values present in two rows in Columns only.

STOREID	Date Time	Sales	Result
A/F3630	02.05.2018 05:07:24	525	525
A/F3635	05.05.2018 09:10:29	101	101
A/F3635	05.05.2018 15:16:24	123	123
A/F3635	05.05.2018  16:52:29	123
A/F3638	11.05.2018 19:24:28	565	565
A/F3639	12.05.2018 18:26:25	141	141

Here the STOREID and DateTime and Sales are 3 fields. When the transaction is happened with in the 2 hours it should be consider as only one transaction.  For above example: STORE ID A/F3635 has done 3 transactions. There are 2 transctions are happend with in the 2 hours. so i considered as only One sales, i.e., 123.

Please find below sample attachment file.

Thanks in advance.

[jyothish8807 · Accepted solution]

This is working for me:

LOAD [Store ID],

     Sales,

     date([Ac.GI date],'MM/DD/YY') as [Ac.GI date],

     time(PGI_Time,'hh:mm:ss') as PGI_Time ,

     interval(timestamp(timestamp#(date([Ac.GI date],'MM/DD/YY')&' '&time(PGI_Time,'hh:mm:ss'),'MM/DD/YY hh:mm:ss'),'MM/DD/YY hh:mm:ss') -

     previous(timestamp(timestamp#(date([Ac.GI date],'MM/DD/YY')&' '&time(PGI_Time,'hh:mm:ss'),'MM/DD/YY hh:mm:ss'),'MM/DD/YY hh:mm:ss')),'h') as New

FROM

(ooxml, embedded labels, table is [Multiple Logic Building], filters(

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5))

));

Br,

KC

[rkpatelqlikview]

This one will be correct answer.  I tried with my Actual Data.

There is Vehiclenumber transactions happened in the same data and time difference is also more than 2 hours, At this criteria NEw Column should get both values. 35 and 35.

If the Time difference is lessthan 2 hours at that time we can consider as only one value.

[rkpatelqlikview]

Great Jyothish, Its working fine. But the problem is here, Even we put the condition peek(Int)<='2', Its not considering the time difference. Below highlighted screenshot the time difference is more than 2 hours. Still the finalQuantity is not taken.

[rkpatelqlikview]

Got it Jyothish,  I very sorry.

Instead Lessthan i put it Greater than.

@ Ravi, Hope you got the answer.

[ravikumar_iyana]

Thanks a Lot Jyothish