topic Re: Row minus next row aggregated by dimension in QlikView

Row minus next row aggregated by dimension

Broly — Sat, 16 Nov 2024 18:01:26 GMT

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

Re: Row minus next row aggregated by dimension

Kushal_Chawda — Fri, 02 Oct 2020 10:56:41 GMT

@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;

Re: Row minus next row aggregated by dimension

Broly — Sat, 03 Oct 2020 12:51:27 GMT

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?

Re: Row minus next row aggregated by dimension

Kushal_Chawda — Sat, 03 Oct 2020 21:06:01 GMT

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

Re: Row minus next row aggregated by dimension

Broly — Sat, 03 Oct 2020 23:14:24 GMT

Hi,

Here is the updated excel

Final column is required

Re: Row minus next row aggregated by dimension

Kushal_Chawda — Sun, 04 Oct 2020 12:33:33 GMT

@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;

Re: Row minus next row aggregated by dimension

Broly — Tue, 06 Oct 2020 08:49:12 GMT

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

Re: Row minus next row aggregated by dimension

Kushal_Chawda — Tue, 06 Oct 2020 10:07:58 GMT

@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;