topic Re: Data Consolidation in QlikView

Data Consolidation

Tue, 14 Jun 2016 09:33:13 GMT

Hi,

Is there a way to consolidate several rows of data in Qlikview?

My data currently looks like this...

Some ID	Unique ID	A	B	C	D	Start	Finish
1	A	-457	-59	-2.95		08/02/2016	05/03/2016
9	A	-117	-8.56	-0.43		08/02/2016	05/03/2016
4	A	0	0	0.3	5.94	08/02/2016	05/03/2016
2	A	0	0	0.34	6.82	06/03/2016	05/04/2016
3	A	106	7.76	0.39		06/03/2016	05/04/2016
7	A	278	35.89	1.79		06/03/2016	05/04/2016
7	A	0	0	0.33	6.6	06/04/2016	05/05/2016
8	A	127	9.3	0.46		06/04/2016	05/05/2016
8	A	331	42.73	2.14		06/04/2016	05/05/2016
9	A	0	0	0.34	6.82	06/05/2016	05/06/2016
9	A	122	8.93	0.45		06/05/2016	05/06/2016
9	A	319	41.18	2.06		06/05/2016	05/06/2016

...and I'd like it to look like this

Unique ID	Consolidated A	Consolidated B	Consolidated C	Consolidated D	Consolidated Start	Consolidated Finish
A	709	78.23	5.22	26.18	08/02/2016	05/06/2016

Re: Data Consolidation

sunny_talwar — Tue, 14 Jun 2016 10:41:16 GMT

for a script based solution, try this:

Table:

LOAD [Unique ID],

Sum(A) as [Consolidated A],

Sum(B) as [Consolidated B],

Sum(C) as [Consolidated C],

Sum(D) as [Consolidated D],

Date(Max(Start)) as [Consolidated Start],

Date(Max(Finish)) as [Consolidated Finish]

Group By [Unique ID];

LOAD [Some ID],

[Unique ID],

Start,

Finish

FROM

[https://community.qlik.com/thread/220709]

(html, codepage is 1252, embedded labels, table is @1);

Re: Data Consolidation

sunny_talwar — Tue, 14 Jun 2016 10:43:57 GMT

For front end solution, check out the attacted

Re: Data Consolidation

Tue, 14 Jun 2016 10:56:36 GMT

Thank you very much for the prompt reply.

This works in the instance however the main check I need is that the dates are continuous. Apologies I didn't make it clear in the original email...

In the following example I expect 2 rows...

Some ID	Unique ID	A	B	C	D	Start	Finish
1	A	-457	-59	-2.95		08/02/2016	05/03/2016
2	A	0	0	0.34	6.82	06/03/2016	05/04/2016
3	A	106	7.76	0.39		06/03/2016	05/04/2016
4	A	0	0	0.3	5.94	08/02/2016	05/03/2016
7	A	278	35.89	1.79		06/03/2016	05/04/2016
7	A	0	0	0.33	6.6	07/04/2016	05/05/2016
8	A	127	9.3	0.46		07/04/2016	05/05/2016
8	A	331	42.73	2.14		06/04/2016	05/05/2016
9	A	-117	-8.56	-0.43		08/02/2016	05/03/2016
9	A	0	0	0.34	6.82	06/05/2016	05/06/2016
9	A	122	8.93	0.45		06/05/2016	05/06/2016
9	A	319	41.18	2.06		06/05/2016	05/06/2016

Unique ID	Consolidated A	Consolidated B	Consolidated C	Consolidated D	Consolidated Start	Consolidated Finish
A	-73	-15.35	-0.13	12.76	08/02/2016	05/04/2016
A	782	93.58	5.35	13.42	07/04/2016	05/06/2016

Re: Data Consolidation

sunny_talwar — Tue, 14 Jun 2016 11:39:06 GMT

Which dates are continuous? Start or Finish or some combination of the two?

Re: Data Consolidation

Tue, 14 Jun 2016 11:45:02 GMT

The finish date of the previous line should be one day before the start date of the current line(provided the Unique ID's are the same).

Re: Data Consolidation

Anonymous — Thu, 27 Apr 2017 14:39:01 GMT

A quick go..

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 FirstWeekDay=0;
SET BrokenWeeks=0;
SET ReferenceDay=4;
SET FirstMonthOfYear=1;
SET CollationLocale='en-GB';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

_tmp.Load:
LOAD [Account Number],
     [Start Date],
     Volume,
     Revenue,
     Tax,
     [Fixed Charge],
     RegisterContent,
     Rate,
     EffectiveFromDate,
     EffectiveToDate,
     EffectiveDays,
     [Actual From],
     [Actual To],
     [Consolidated Volume],
     [Consolidated Revenue],
     [Consolidated Fixed Charge],
     [Actual EffectiveDays],
     [Consolidated Tax]
From
[Kingsley.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate
_tmp2.Load:
LOAD [Account Number],
     [Start Date],
     Volume,
     Revenue,
     Tax,
     [Fixed Charge],
     RegisterContent,
     Rate,
     EffectiveFromDate,
     EffectiveToDate,
     If(IsNull(If(Peek([Account Number])<>[Account Number] or EffectiveFromDate > Peek(EffectiveToDate)+1,AutoNumber([Account Number]&EffectiveFromDate))),
      Peek([Time ID]),
      If(Peek([Account Number])<>[Account Number] or EffectiveFromDate > Peek(EffectiveToDate)+1,AutoNumber([Account Number]&EffectiveFromDate))
     )                                     as [Time ID],
     EffectiveDays,
     [Actual From],
     [Actual To],
     [Consolidated Volume],
     [Consolidated Revenue],
     [Consolidated Fixed Charge],
     [Actual EffectiveDays],
     [Consolidated Tax]
Resident _tmp.Load
Order By
[Account Number],
EffectiveFromDate;

DROP Table _tmp.Load;

NoConcatenate
_tmp3.Load:
LOAD [Account Number],
[Time ID],
     Min([Start Date])     as [Start Date],
     Sum(Volume)      as Volume,
     Sum(Revenue)      as Revenue,
     Sum(Tax)       as Tax,
     Sum([Fixed Charge])    as [Fixed Charge],
     Concat(RegisterContent,', ')   as RegisterContent,
     Sum(Rate)/
     Sum(
      If([Fixed Charge]=0,
       0,
       EffectiveDays)
      )        as Rate,
     Min(EffectiveFromDate)    as EffectiveFromDate,
     Max(EffectiveToDate)    as EffectiveToDate,
     Sum(EffectiveDays)     as EffectiveDays,
     Min([Actual From])     as [Actual From],
     Max([Actual To])     as [Actual To],
     Sum([Consolidated Volume])   as [Consolidated Volume],
     Sum([Consolidated Revenue]) as [Consolidated Revenue] ,
     Sum([Consolidated Fixed Charge]) as [Consolidated Fixed Charge],
     Sum([Actual EffectiveDays]) as [Actual EffectiveDays],
     Sum([Consolidated Tax])   as [Consolidated Tax]
Resident _tmp2.Load
Group By
[Account Number],
[Time ID];

DROP Table _tmp2.Load;