Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Data Consolidation

Hi,

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

My data currently looks like this...        

Some IDUnique IDABCDStart Finish
1A-457-59-2.9508/02/201605/03/2016
9A-117-8.56-0.4308/02/201605/03/2016
4A000.35.9408/02/201605/03/2016
2A000.346.8206/03/201605/04/2016
3A1067.760.3906/03/201605/04/2016
7A27835.891.7906/03/201605/04/2016
7A000.336.606/04/201605/05/2016
8A1279.30.4606/04/201605/05/2016
8A33142.732.1406/04/201605/05/2016
9A000.346.8206/05/201605/06/2016
9A1228.930.4506/05/201605/06/2016
9A31941.182.0606/05/201605/06/2016

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

       

Unique IDConsolidated AConsolidated BConsolidated CConsolidated DConsolidated StartConsolidated Finish
A70978.235.2226.1808/02/201605/06/2016
6 Replies

Re: Data Consolidation

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],

     A,

     B,

     C,

     D,

     Start,

     Finish

FROM

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

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

Re: Data Consolidation

For front end solution, check out the attacted

Capture.PNG

Not applicable

Re: Data Consolidation

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 IDUnique IDABCDStart Finish
1A-457-59-2.9508/02/201605/03/2016
2A000.346.8206/03/201605/04/2016
3A1067.760.3906/03/201605/04/2016
4A000.35.9408/02/201605/03/2016
7A27835.891.7906/03/201605/04/2016
7A000.336.607/04/201605/05/2016
8A1279.30.4607/04/201605/05/2016
8A33142.732.1406/04/201605/05/2016
9A-117-8.56-0.4308/02/201605/03/2016
9A000.346.8206/05/201605/06/2016
9A1228.930.4506/05/201605/06/2016
9A31941.182.0606/05/201605/06/2016

Unique IDConsolidated AConsolidated BConsolidated CConsolidated DConsolidated StartConsolidated Finish
A-73-15.35-0.1312.7608/02/201605/04/2016
A78293.585.3513.4207/04/201605/06/2016

Re: Data Consolidation

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

Not applicable

Re: Data Consolidation

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

charlie_mstrmnd
New Contributor III

Re: Data Consolidation

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;

Community Browser