Calculate difference between Timestamps with above

[amber2000]

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

[amber2000]

For this I'm working with an personal edition

[sunny_talwar]

Update to personal edition QV12?

[amber2000]

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

[sunny_talwar]

Try this:

Where is this field -> SSCC_OUT?

[amber2000]

Forgot to copy it, Now it's there

[sunny_talwar]

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;

[amber2000]

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 ?

[sunny_talwar]

Can you share this newly refreshed application?

[amber2000]

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

[swuehl]

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