script for sum of fields in special order and rules

[#1]

Hello,

I need some help ...

I've got a table, with le list of the statuts and date for ID.

the same ID can have différents statuts at différents date.

I have also the end date and the duration of each statut.

But now I would like to calculate the sum of the duration of each ID, with theses specifics rules :

• from the first date I'm going in the ascending time, I add the duration of the statuts 'new', 'affected', 'Reopen'. I do not add the duration of the statut 'question to customer'.
• AND I STOP the sum when the statut is Closed or Accepted , even if there are other statut 'new', 'affected', 'Reopen' in the dates after this one
• for the two ID of the joined example, the duration is the sum of the yellow lines, the white line is not added, and the count stop to the blue line. The green line are not in the sum because they are after the blue line.

Thank you

stephane

[swuehl · Accepted solution]

stephane,

there are probably some more elegant solutions, but you can start with something like this:

INPUT:

LOAD Id,

     Statut,

     [begin Date],

     [End date],

     duration

FROM

[.\example.xlsx]

(ooxml, embedded labels);

//Flag the records to ignore

TMP:

LOAD Id, Statut,[begin Date],[End date],duration,

if(peek(StopFlag) and peek(Id) = Id, 1,StopFlag) as StopFlag;

LOAD *, if(Statut='Closed' or Statut='Accepted',1) as StopFlag

Resident INPUT where not Statut like  'Question*' order by Id, [begin Date] asc;

//Calculate the sum of durations grouped by Id

RESULT:

LOAD Id, interval(sum(duration)) as SumDuration,count(duration) as CountDuration

resident TMP where isnull(StopFlag) group by Id;

drop Table TMP;

Regards,

Stefan

[#3]

Wonderful!

great catch

You are amazing swuehl

Thank you very much.

stephane

[#4]

One more question about this isue...

I would like also the date of stopflag.

for example for the ID 145554 this date is the begin date of the blue line (Accepted : 8/1/12)

I tried with :

interval(max([begin_date])) as [Date StopFlag]

but t've got only the date of the last one before the blue line which is not 'question to customer'.

[#5]

I've found it.

I have to use where not isnull(StopFlag) for catching the right line...