Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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.

ScreenHunter_28 Sep. 21 21.32.gif

Thank you

stephane

1 Solution

Accepted Solutions
MVP
MVP

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:

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

4 Replies
MVP
MVP

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:

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

Not applicable

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

Wonderful!

great catch

You are amazing swuehl

Thank you very much.

stephane

Not applicable

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

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

Not applicable

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