Solved: Re: Group data by consecutive dates - Qlik Community

Report Inappropriate Content · ‎2014-07-11

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

maxgro · ‎2014-07-11

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;

View solution in original post

Gysbert_Wassenaar · ‎2014-07-11

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;

talk is cheap, supply exceeds demand

maxgro · ‎2014-07-11

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 · ‎2014-07-11

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 · ‎2014-07-11

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;

Report Inappropriate Content · ‎2014-07-11

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) ?

Report Inappropriate Content · ‎2014-07-11

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

maxgro · ‎2014-07-11

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;

Report Inappropriate Content · ‎2014-07-11

Exactly yes. Thank you

Report Inappropriate Content · ‎2014-07-11

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