topic Re: Intervals and segmentation in QlikView

Intervals and segmentation

Wed, 29 May 2013 16:25:22 GMT

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

Re: Intervals and segmentation

Fri, 07 Jun 2013 07:55:04 GMT

up !

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

Re: Intervals and segmentation

hic — Fri, 07 Jun 2013 08:27:40 GMT

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

Re: Intervals and segmentation

Wed, 12 Jun 2013 16:56:28 GMT

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;

Re: Intervals and segmentation

hic — Wed, 12 Jun 2013 21:15:53 GMT

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

Re: Intervals and segmentation

Thu, 13 Jun 2013 15:55:04 GMT

Wonderfull ! thanks alot !