Intervals and segmentation

[#1]

Hi,

I have used the excellent tutorial of Swuehl to calculate cumulated intervals between different dates.

http://community.qlik.com/message/348643

In my case those dates are incidents (application downtime)

I'd like now to capitalize on this basis and extend the analysis by using segmentations (by business and months)

Each application can by used by several businesses.

This means that the duration of an incident can be duplicated for each businesses that is using the application

I have created the following case :

// working days : Mon-Fri 8:00-20:00

// This case doesn’t take into account holidays

SET TimestampFormat = 'M/D/YYYY hh:mm';

Business:

LOAD * INLINE [

Business,   Application

Designer,  Photoshop

Designer,  Outlook

Designer,  3DSMax

Hotliner,  Outlook

Hotliner,  Skype

];

Incidents:

LOAD * INLINE [

Incident, Start,      End,   Application

Incident 1,  1/1/2013 10:00,    1/1/2013 14:00  Photoshop

Incident 2,   1/1/2013 12:00,    1/1/2013 15:00,   Outlook

Incident 3,   1/1/2013 18:00,    1/2/2013 10:00,   Outlook

Incident 5, 1/2/2013 09:00,    1/2/2013 10:00,   3DSMax

Incident 4, 1/1/2013 17:00,    1/2/2013 16:00,  Skype

];

The output i'm expecting is the folowing

Month  Business  Impact

January  Designer  09:00

January  Hotliner    16:00

I can join my entire code if needed.

Anyone has an idea ?

Thanks and regards

[#2]

up !

No one has even exeprienced this case (it must be a standard issue in industry) ?

[hic]

I would first generate the subintervals using partitioning as described in http://community.qlik.com/docs/DOC-4310.

Once this is done, you can create the following data model, with the field "Duration" as the one you want to sum.

HIC

[#4]

Hi, your tutorials are awsome !

I have tryed to adapt several steps but I don't get it with intervalmatch, could you please tell me what is going wrong ?

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

Business:

LOAD * INLINE [

          Business,  ApplicationId

          Designer,  Photoshop

          Designer,  Outlook

          Designer,  3DSMax

          Hotliner,  Outlook

          Hotliner,  Skype

];

IncidentsSRC:

LOAD * INLINE [

          IncidentNo, Start,                                                End,                                  ApplicationId

          Incident 1,  01/01/2013 10:00,    01/01/2013 14:00,   Photoshop

          Incident 2,  01/01/2013 12:00,    01/01/2013 15:00,   Outlook

];

Let vEpsilon                     = Pow(2,-27);

Let vEndTime                     = now();

Incidents:

Load *,

           hash128(IncidentBegin, IncidentEnd) as IncidentKey;

Load

           ApplicationId,

          TimeStamp(TimeStamp#(Start,'DD/MM/YYYY hh:mm')) as IncidentBegin,

          TimeStamp(TimeStamp#(End,'DD/MM/YYYY hh:mm')) as IncidentEnd

RESIDENT IncidentsSRC

;

TempSubIntervals:

Load distinct ApplicationId, Start as SubIntervalBegin RESIDENT IncidentsSRC;

Load distinct ApplicationId, End   as SubIntervalBegin RESIDENT IncidentsSRC;

Drop table IncidentsSRC;

// Create intervals from single dates.

SubIntervals:

Load

          hash128(SubIntervalBegin) as SubIntervalKey

          , SubIntervalBegin

          , If(isnull(previous(SubIntervalBegin))=0,TimeStamp(TimeStamp#(Previous(SubIntervalBegin),'DD/MM/YYYY hh:mm')-$(#vEpsilon)) , ($(#vEndTime))) as SubIntervalEnd

Resident TempSubIntervals

Order By SubIntervalBegin Desc;

Drop Table TempSubIntervals;

// ============ Create the bridge table between the subintervals and the incidents ============

TmpIncidentBridgeTable:

IntervalMatch (SubIntervalBegin)

Load distinct IncidentBegin, IncidentEnd resident Incidents;

IncidentBridgeTable:

Load

           hash128(IncidentBegin, IncidentEnd) as IncidentKey,

          hash128(SubIntervalBegin) as SubIntervalKey

          Resident TmpIncidentBridgeTable;

// ============ Cleanup ============

Drop table TmpIncidentBridgeTable;

[hic · Accepted solution]

You were almost right. There were however a couple of timestamps that were not interpreted as dates, som I moved a couple of Timestamp#() functions to get it right. Further, I subtracted an "epsilon" from the IncidentEnds. And finally , I added the field Duration. And now it works.

HIC

[#6]

Wonderfull ! thanks alot !