Row minus next row aggregated by dimension

[Broly]

Hello all,

please take a look on excel file which I have attached.

I want to get duration column like it is shown in the file, based on the timestamps in the file.

Those zeroes are last timestamp minus itself.

Thanks

[Kushal_Chawda]

@Broly  try below. Assuming your timestamp field is in proper timestamp format and not text. 

Data:
LOAD Shipment, 
     EXTERNAL_PACKAGE_ID, 
     FIRST_TERMINAL_ACTIVITYTIME
FROM
[C:\Question.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(Data)
LOAD Shipment,
     timestamp(max(FIRST_TERMINAL_ACTIVITYTIME)) as Min_FIRST_TERMINAL_ACTIVITYTIME
Resident Data
Group by Shipment;

Final:
NoConcatenate
LOAD *,
     if(FIRST_TERMINAL_ACTIVITYTIME=Min_FIRST_TERMINAL_ACTIVITYTIME,dual('0m',0),dual(minute(Peek(FIRST_TERMINAL_ACTIVITYTIME)-FIRST_TERMINAL_ACTIVITYTIME)&'m',
     Peek(FIRST_TERMINAL_ACTIVITYTIME)-FIRST_TERMINAL_ACTIVITYTIME)) as Minutes
Resident Data
Order by FIRST_TERMINAL_ACTIVITYTIME desc;

DROP Table Data;

[Broly]

Thank you, but this is not quite what I need. The result was just an example, it does need to be formated strictly like that. Also when I supplied a larger set of data, I got a lot of strange results. How can I perform Peek() grouped by a dimmension?

[Kushal_Chawda]

@Broly  Not sure what you need but you can share sample data with expected output

[Broly]

Hi,

Here is the updated excel

Final column is required

[Kushal_Chawda]

@Broly  slight change to previous script will give you what you want. Your time field format is "hh:mm:ss" in your data so you need to set the below variable in main tab of the script with same format

SET TimeFormat='hh:mm:ss';

Now you can try below script

Data:
LOAD * Inline [
Shipment	EXTERNAL_PACKAGE_ID	FIRST_TERMINAL_ACTIVITYTIME
1	DN1	11:03:00
1	DN2	11:07:07
1	DN3	11:08:00
2	DN4	11:11:15
2	DN5	11:15:00 ] (delimiter is '\t');

Left Join(Data)
LOAD Shipment,
     time(max(FIRST_TERMINAL_ACTIVITYTIME)) as Min_FIRST_TERMINAL_ACTIVITYTIME
Resident Data
Group by Shipment;

Final:
NoConcatenate
LOAD *,   if(FIRST_TERMINAL_ACTIVITYTIME=Min_FIRST_TERMINAL_ACTIVITYTIME,null(),Peek(FIRST_TERMINAL_ACTIVITYTIME)) as Final
Resident Data
Order by FIRST_TERMINAL_ACTIVITYTIME desc;

DROP Field Min_FIRST_TERMINAL_ACTIVITYTIME;

DROP Table Data;

[Broly]

@Kushal_Chawda Hi there, first I would like to thank you for your efforts to help me. 

I am using this data set, and I will post a screenshot what data comes out.

[Kushal_Chawda · Accepted solution]

@Broly  try below

Data:
LOAD H_ROUTE_NAME, 
     bi_shipment_packages.BARCODE_VALUE, 
     SHIPMENT_ID, 
     FIRST_TERMINAL_ACTIVITYTIME
FROM
[C:\dod.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(Data)
LOAD SHIPMENT_ID,
     Count(FIRST_TERMINAL_ACTIVITYTIME) as Cnt_FIRST_TERMINAL_ACTIVITYTIME,
     Timestamp(max(FIRST_TERMINAL_ACTIVITYTIME)) as Max_FIRST_TERMINAL_ACTIVITYTIME
Resident Data
Group by SHIPMENT_ID;

Final:
NoConcatenate
LOAD *,   
     if(Cnt_FIRST_TERMINAL_ACTIVITYTIME=1,FIRST_TERMINAL_ACTIVITYTIME,
     if(FIRST_TERMINAL_ACTIVITYTIME=Max_FIRST_TERMINAL_ACTIVITYTIME,null(),peek(FIRST_TERMINAL_ACTIVITYTIME))) as Final
Resident Data
Order by SHIPMENT_ID,FIRST_TERMINAL_ACTIVITYTIME desc;

DROP Fields Max_FIRST_TERMINAL_ACTIVITYTIME,Cnt_FIRST_TERMINAL_ACTIVITYTIME;

DROP Table Data;