topic Re: Calculate difference between Timestamps with above in QlikView

Calculate difference between Timestamps with above

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

Hi Everyone,

I want to calculate the difference between 2 times based on the field SSCC_OUT.

I have 2 fields with a Time Stamp but the are identical on the same row.

Example:

SSCC_OUT StartingTime EndingTime Time_Diff

112345030000034095 22:10:27 22:10:27 00:00:00

112345030000034095 22:11:10 22:11:10 00:00:00

Example of what I would like it to be:

SSCC_OUT StartingTime EndingTime Time_Diff

112345030000034095 22:10:27 22:10:27 00:00:00

112345030000034095 22:11:10 22:11:10 00:00:43 (difference with the above function)

I've attached a qvw with my efforts on the above() function and so far the result stays 00:00:00

Can anybody help me please?

Kind regards,

Monique

Re: Calculate difference between Timestamps with above

Chanty4u — Tue, 22 Mar 2016 12:48:38 GMT

hi,

https://community.qlik.com/message/437410#437410

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 13:01:46 GMT

May be this:

=Time(rangesum(StartingTime - Aggr(Above(StartingTime), SSCC_OUT, StartingTime)), 'hh:mm:ss')

Re: Calculate difference between Timestamps with above

amber2000 — Tue, 22 Mar 2016 14:34:17 GMT

Sunny,

Sorry that my answer comes so late, I was testing your expression on a whole lot more data then I've send before.

It's there that I see something is wrong.

I'll send a new attach so you can see for yourself.

Every expression you've tried seems to give wrong results as I use it on my original data.

Kind regards,

Monique

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 14:43:57 GMT

Not sure if you are using QV 12 or not, but this can work if you are using QV 12

=Time(RangeSum(StartingTime - Aggr(Above(StartingTime), (SSCC_OUT, (TEXT)), (StartingTime, (NUMERIC)))), 'hh:mm:ss')

Re: Calculate difference between Timestamps with above

amber2000 — Tue, 22 Mar 2016 15:05:47 GMT

Hi Sunny,

I'm using version 11.20 SR10

I can't see your changes, the functions TEXT and NUMERIC don't seem to be recognized.

Isn't there something else for QV 11

Kind regards,

Monique

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 15:45:18 GMT

Its a new introduction, included in QV 12. For this to work in version before QV 12, you will need to Sort your date correctly in the load script.

This is the output, I was seeing using the expression I gave you

Although the expression editor shows me the error, I do get the output I am expecting

To read about this new syntax, read here: The sortable Aggr function is finally here!

The problem is that Aggr function sorts by the load order and you can fix the sort order in the script to fix the issue, or you need to have QV12 to fix it in the expression itself

Re: Calculate difference between Timestamps with above

amber2000 — Tue, 22 Mar 2016 16:15:30 GMT

Dear Sunny,

It's hopeless, it's not working as you can see.

Where in the script can I fix the sorting issue? In the sort tab of the pivot table I put SSCC_OUT first and StartingTime second.

The sort tab:

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 16:18:39 GMT

That is not going to help. Fix will have to be made in the script where you will have to use Order By Statement in a resident load to make sure that load order is like this:

Order By SSCC_OUT, StartingTime;

In the pivot table itself is not going to work as far as I know

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 16:19:02 GMT

Updating to QV12 is not a option for you?

Re: Calculate difference between Timestamps with above

amber2000 — Tue, 22 Mar 2016 16:20:16 GMT

For this I'm working with an personal edition

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 16:22:37 GMT

Update to personal edition QV12?

Re: Calculate difference between Timestamps with above

amber2000 — Tue, 22 Mar 2016 16:25:47 GMT

Can you please advise me on where I shall put the order by in this piece of loadscript:

Prod:

LOAD

pickDate,

pickUser,

pickType,

casesPickedSum,

PiecesPickedSum,

pickingLinesCount,

time(StartingTime) AS StartingTime,

time(EndingTime) AS EndingTime,

SSCC_OUT

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Kind regards,

Monique

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 16:27:59 GMT

Try this:

Where is this field -> SSCC_OUT?

Re: Calculate difference between Timestamps with above

amber2000 — Tue, 22 Mar 2016 16:30:12 GMT

Forgot to copy it, Now it's there

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 16:31:07 GMT

Try this:

Table:

LOAD pickDate,

pickUser,

pickType,

casesPickedSum,

PiecesPickedSum,

pickingLinesCount,

time(StartingTime) as StartingTimeTemp,

time(EndingTime) as EndingTime,

Assortment,

PickingLineID,

SSCC_OUT as SSCC_OUT_Temp,

ArticleID,

Store,

Article_Desc

FROM

[Picking lines van Del_Day 15-17032016.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD

pickDate,

pickUser,

pickType,

casesPickedSum,

PiecesPickedSum,

pickingLinesCount,

StartingTimeTemp as StartingTime,

time(EndingTime) as EndingTime,

Assortment,

PickingLineID,

SSCC_OUT_Temp as SSCC_OUT,

ArticleID,

Store,

Article_Desc

Resident Table

Order By SSCC_OUT_Temp, StartingTimeTemp;

DROP Table Table;

Re: Calculate difference between Timestamps with above

amber2000 — Tue, 22 Mar 2016 16:48:43 GMT

Sunny,

Slowly I'm getting there thanks to you, the Expression now provides a value in the table

There is still a problem with the calculation of the time difference, the values are completely wrong.

Between the first 2 the difference should be 00:03:16 instead of 00:00:46 ?

Re: Calculate difference between Timestamps with above

sunny_talwar — Tue, 22 Mar 2016 16:51:06 GMT

Can you share this newly refreshed application?

Re: Calculate difference between Timestamps with above

amber2000 — Tue, 22 Mar 2016 17:05:04 GMT

I think I'm losing my mind, It's not working at all in QV 11

The screenshot came from a QV 12 to test for both solutions. The version 12 I can't use because it's on another Server that's not (and never will be) connected to this datasource.

The attach is from version 11

Re: Calculate difference between Timestamps with above

swuehl — Tue, 22 Mar 2016 17:29:00 GMT

Looks ok to me using as expression

Aggr(Above(StartingTime), SSCC_OUT,StartingTime)

and your changed script.

If you want to validate the data, you need to sort your table also by SSCC_OUT and StartingTime (which it's not sorted by StartingTime in your screenshot).

Hope this helps,

Stefan

edit:

Attached QVW