sumifs with timestamps, in script

[#1]

Hi All,

I have browsed through a lot of posts about sum if scripting/set analysis, also tried some of the answers given, though I cannot make my specific case work, therefore I am posting my question.

I have multiple data sets (see underneath) which give timestamps of train trips. locomotive number and KwH usage and I need to have the sum of KwH usage per TRAINRUN_ID. Now there are two key variables/conditions: Loco and Timestamps arrival - Timestamps departure. I need to divide the KwH usage based on these conditions -> = Loco Number and that the DateTime is in between the above timestamps.

Min_Max_Table:

Load  

      TRAINRUN_ID,

      Min_Timestamp_Departure,

      Max_Timestamp_Arrival

Resident

     EBIS_TRS

GROUP BY

     TRAINRUN_ID;

right join(EBIS_TRSB)

LOC:

load TRSB_ID,    (TRSB is eventually coupled with a TRAIN_ID like in the previous table)

       LOC_NO

Electricity:

Load

LOC,

DateTime,

KwH_Usage_Postive,

FROM ..

You need to divide based on the LOC number because that number is coupled with a TRAINRUN_ID. I tried to do something like:

sum(If(DateTime>=Min_Timestamp_Departure and DateTime<=Max_Timestamp_Arrival and LOC = LOC_NO , KwH_Usage_Postive , 0)

Obviously this did not work, maybe I have to use something else than a sumifs formula, please help me .

[jonathandienst]

I suggest that you upload a sample qvw containing some representative data.