6 Replies Latest reply: Apr 27, 2017 10:39 AM by Charlie Hudson RSS

    Data Consolidation

    Kingsley Ugwu

      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
        • Re: Data Consolidation
          Sunny Talwar

          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
              Sunny Talwar

              For front end solution, check out the attacted

               

              Capture.PNG

              • Re: Data Consolidation
                Kingsley Ugwu

                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
                Charlie Hudson

                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;