4 Replies Latest reply: Sep 22, 2012 4:46 PM by stephane jeanneteau

# script for sum of fields in special order and rules

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

• ###### Re: script for sum of fields in special order and rules

stephane,

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

INPUT:

Statut,

[begin Date],

[End date],

duration

FROM

[.\example.xlsx]

(ooxml, embedded labels);

//Flag the records to ignore

TMP:

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

• ###### Re: script for sum of fields in special order and rules

Wonderful!

great catch

You are amazing swuehl

Thank you very much.

stephane

• ###### Re: script for sum of fields in special order and rules

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

• ###### Re: script for sum of fields in special order and rules

I've found it.

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