Group data by consecutive dates

[#1]

I got this requirement from the business which is quite tricky, and I would like to know whether it is possible to handle with Qlikview (if yes, in which stage) or not since I started to know Qlikview a month ago only.

The table stores the events with:

Event_ID (unique number), Event_Description, Event_Organiser, Event_Date

Event_ID	Event_Description	Event_Organiser	Event_Date
1	A	X	08.07.2014
2	A	X	09.07.2014
3	A	X	10.07.2014
4	B	Y	12.07.2014
5	A	X	15.07.2014

An entry is created in the DB per event and per day and we need to regroup the events by the consecutive days, which means,

if the event has the same description and organiser, we take the consecutive dates and consider that the event last for N days

The required outcome is as following (if possible assign a group ID which is the "real" Event ID and not the numeric one generated by the DB)

Event_Description  Event_Organiser  Event_Date

A                              X                          08.07.2014-10.07.2014

A                              X                          15.07.2014

B                              Y                          12.07.2014

Thank you for any suggestion

[Gysbert_Wassenaar]

Something like this perhaps:

Temp:
Load
    Event_ID,
    Event_Description,
    Event_Organiser,
    Event_Date
From ...sourcedata...;

Result:
Load
    Event_ID,
    Event_Description,
    Event_Organiser,
    Event_Date
    if(Event_Description & '|' & Event_Organiser = previous(Event_Description & '|' & Event_Organiser),peek('RealID'),rangesum(1,peek('RealID'))) as RealID
Resident Temp
Order by Event_Date,Event_Description,Event_Organiser;

Drop Table Temp;

[maxgro]

result

if you only want the first NEWID (1,5,4) add another load distinct to the end

script

s:

LOAD Event_ID, Event_Description, Event_Organiser, Event_Date,

Event_Description & '-' & Event_Organiser as NEW

FROM [http://community.qlik.com/thread/125315] (html, codepage is 1252, embedded labels, table is @1);

t:

NoConcatenate load *,

if (NEW=peek(NEW) and Event_Date = Peek(Event_Date)+1, Peek(NEWID), Event_ID) as NEWID

Resident s

Order by NEW, Event_Date;

DROP Table s;

final:

Left join (t)

load

  NEWID,

  date(min(Event_Date)) & ' - ' & date(max(Event_Date)) as NEWDATE

Resident

  t

group by NEWID;

[MK_QSL]

Hope this script will help you..

==================

Temp:

Load

  Event_ID,

  Event_Description,

  Event_Organiser,

  Event_Description&Event_Organiser as Key,

  DATE(DATE#(Event_Date,'DD.MM.YYYY')) as Event_Date

Inline

[

  Event_ID, Event_Description, Event_Organiser, Event_Date

  1, A, X, 08.07.2014

  2, A, X, 09.07.2014

  3, A, X, 10.07.2014

  4, B, Y, 12.07.2014

  5, A, X, 15.07.2014

];

Temp2:

Load

  Date(IF(RowNo()=1, Event_Date, IF(Key = Previous(Key) and Date(Event_Date) = Date(Previous(Event_Date)+1),RangeSum(Peek(MaxDate), 1),Event_Date))) as MaxDate,

  Date(IF(RowNo()=1, Event_Date, IF(Key = Previous(Key) and Date(Event_Date) = Date(Previous(Event_Date)+1),RangeSum(Peek(MinDate)),Event_Date))) as MinDate,

  Date(IF(RowNo()=1, 1, IF(Key = Previous(Key) and Date(Event_Date) = Date(Previous(Event_Date)+1),RangeSum(Peek(TotalDays),1),1))) as TotalDays,

  Key

Resident Temp

Order By Key, Event_Date;

Drop Table Temp;

Join

Load

  Key,

  MinDate,

  Max(TotalDays) as MTD

Resident Temp2

Group By Key, MinDate;

NoConcatenate

Final:

Load * Resident Temp2 Where MTD = TotalDays;

Drop Field MTD;

Drop Table Temp2;

==============================

[MK_QSL]

Or another.... better than previous one...

=======================

Temp:

Load

  Event_ID,

  Event_Description,

  Event_Organiser,

  Event_Description&'|'&Event_Organiser as Key,

  DATE(DATE#(Event_Date,'DD.MM.YYYY')) as Event_Date

Inline

[

  Event_ID, Event_Description, Event_Organiser, Event_Date

  1, A, X, 08.07.2014

  2, A, X, 09.07.2014

  3, A, X, 10.07.2014

  4, B, Y, 12.07.2014

  5, A, X, 15.07.2014

];

Temp2:

Load

  Date(IF(RowNo()=1, Event_Date, IF(Key = Previous(Key) and Date(Event_Date) = Date(Previous(Event_Date)+1),RangeSum(Peek(MaxDate), 1),Event_Date))) as MaxDate,

  Date(IF(RowNo()=1, Event_Date, IF(Key = Previous(Key) and Date(Event_Date) = Date(Previous(Event_Date)+1),RangeSum(Peek(MinDate)),Event_Date))) as MinDate,

  Date(IF(RowNo()=1, 1, IF(Key = Previous(Key) and Date(Event_Date) = Date(Previous(Event_Date)+1),RangeSum(Peek(TotalDays),1),1))) as TotalDays,

  Key

Resident Temp

Order By Key, Event_Date;

Drop Table Temp;

Join

Load

  Key,

  MinDate,

  Max(TotalDays) as MTD

Resident Temp2

Group By Key, MinDate;

NoConcatenate

Final:

Load

  SubField(Key,'|',1) as Event_Description,

  SubField(Key,'|',2) as Event_Organiser,

  MaxDate,

  MinDate,

  TotalDays

Resident Temp2 Where MTD = TotalDays;

Drop Field MTD;

Drop Table Temp2;

[#6]

Hello Manish,

Your solution sounds simple that I'd like to follow and make a test...

Is it possible then to concatenate the original event_ID (like '1-2-3' for event A from 08-10.07.2014) ?

[#7]

Thank you for your quick response. Let me make some tests with your suggestion. Thank you

[maxgro · Accepted solution]

in that way?

add a concat in bold

final:

Left join (t)

load

  NEWID,

  date(min(Event_Date)) & ' - ' & date(max(Event_Date)) as NEWDATE,

concat(Event_ID, '-') as NEWEVENTID

Resident

  t

group by NEWID;

[#9]

Exactly yes. Thank you

[#10]

Hey, I think you forgot the condition on the consecutive date. I made a test in real data (with more than 10 similar fields) and there is no common RealID given.

I gonna try the solution of Massimo