Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 :
Thank you
stephane
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
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
Wonderful!
great catch
You are amazing swuehl
Thank you very much.
stephane
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'.
I've found it.
I have to use where not isnull(StopFlag) for catching the right line...