topic Re: Group data by consecutive dates in QlikView

Group data by consecutive dates

Fri, 11 Jul 2014 07:38:50 GMT

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

Re: Group data by consecutive dates

Gysbert_Wassenaar — Fri, 11 Jul 2014 08:14:55 GMT

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;

Re: Group data by consecutive dates

maxgro — Fri, 11 Jul 2014 08:20:04 GMT

result

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

script

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

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

group by NEWID;

Re: Group data by consecutive dates

MK_QSL — Fri, 11 Jul 2014 08:33:39 GMT

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;

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

Re: Group data by consecutive dates

MK_QSL — Fri, 11 Jul 2014 08:36:50 GMT

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;

Re: Group data by consecutive dates

Fri, 11 Jul 2014 09:00:22 GMT

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

Re: Group data by consecutive dates

Fri, 11 Jul 2014 09:02:16 GMT

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

Re: Group data by consecutive dates

maxgro — Fri, 11 Jul 2014 09:08:15 GMT

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

group by NEWID;

Re: Group data by consecutive dates

Fri, 11 Jul 2014 10:55:52 GMT

Exactly yes. Thank you

Re: Group data by consecutive dates

Fri, 11 Jul 2014 15:06:17 GMT

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

Re: Group data by consecutive dates

MK_QSL — Fri, 11 Jul 2014 15:08:42 GMT

Have you tried my one? 🙂

Re: Group data by consecutive dates

Fri, 11 Jul 2014 15:26:49 GMT

Hi all, many thanks for your suggestion. I finish my working day now (was busy to solve some issues) and did not have time to try all proposals yet. Please let me sometime (also I am slow as beginner ). I don't have Qlik at home to work with so it will be next Monday

Wish you good weekend

Re: Group data by consecutive dates

Mon, 14 Jul 2014 14:15:47 GMT

Hello Manish, I'm sorry to disappoint you but I did follow the solution from Massimo since it's bright clear to understand (no usage of SubField or RowNum, ....) and I will not have time to test your proposal. Thank you and best wishes

Re: Group data by consecutive dates

Mon, 14 Jul 2014 14:17:56 GMT

Hi Massimo, thank you again for your solution that I appreciated alot for many reasons:

- create a key field (NEW) for comparaison (indeed I have more than 10 fields to compare .-)

- Add additional column to combine the ID and dates

Re: Group data by consecutive dates

MK_QSL — Tue, 15 Jul 2014 04:45:12 GMT

As far as you got answer is fine to me. Community members satisfaction is more important than my point.

Re: Group data by consecutive dates

Tue, 15 Jul 2014 06:57:08 GMT

Response appreciated 😉