3 Replies Latest reply: Mar 4, 2010 6:56 PM by Steve Dark RSS

    How to summarise records during load?

      Hello,

      I may be trying to approach this in completely the wrong way, but i have the following scenario that i could do with some advice on:

      I have one table that i am loading which contains a list of Calls e.g.:

      Calls:

      ID, Open Date, Close Date

      Call1, 01/03/2010, 02/03/2010

      Call2, 01/03/2010, 01/03/2010

      etc...

      I then have another table that I load which contains the same 1 or more rows for each call along with the 'Companies' that the Call Affected, this covers a total of 2 companies e.g.

       

      ID, Location

      Call1, Company2

      Call1, Company1

      Call2, Company1

      etc....

       

      What i need to produce is a summary showing the number of Calls that affected

      1. Just Company1

      2. Just Company2

      3. Both Companies

       

      I have done similar analysis in excel and i do this by reading in a VBA macro each Company value for each row related to each call, and creating a Summary value of Comany1, Company2 or Both, i then store this value along with the CallID in a seperate table so i have a single row of data for each call, which i can then analyse: e.g.

      Summary Table:

      ID, Open Date, Close Date, AffectedCompanySummary

      Call1, 01/03/2010, 02/03/2010, Both

      Call2, 01/03/2010, 01/03/2010, Company1

       

      Can i do something during my load into Qlikview, I dont want to lose the granularity of the data though, or is there a smarter way of doing this?

      Can someone point me in the right direction?

       

      Many thanks!!

       

        • How to summarise records during load?
          Peter Rieper

          Solution might be depending a bit on the real data, assuming that the second table has for each call max one entry for either company1 or company2 (or both), you may work with multiples of 2: assign value 1 to company1, value 2 to company2 (value 4 to company3) and then sum up the values. If result is 1, you have only company1. If result is 2, there is only company2, if 3 they are mixed.

          HTH
          Peter

            • How to summarise records during load?

              Thanks Peter, but i've proabably oversimplified things above. I pull the info from a SQL database, and there can be up to 50 diffrent rows in the second table relating to various departments of companies 1 & 2.

              Is there any way of reading the lines of data as i bring them in, in some kind of loop FOR ... NEXT... etc... to create rows in a new 'summary' table?

              I have other similar situations where i can see this requirement as well.

               

            • How to summarise records during load?
              Steve Dark

              Hi there,

              If you can be sure that there will only ever be two companies in the second table you could use a couple of mapping loads onto the main call table. It would be something like this:

              Map_Company1Calls:
              MAPPING LOAD
              ID,
              1 as CallCount
              FROM CallCompanies
              WHERE Location = 'Company1';

              [Similar mapping load for Company 2 Calls]

              Calls:
              LOAD
              ID,
              [Open Date],
              [Close Date],
              ApplyMap('Map_Company1Calls', ID, 0) as Company1Count,
              ApplyMap('Map_Company2Calls', ID, 0) as Company2Count
              FROM Calls;

              Once you've done that you've two fields you can sum for Company 1 Count and Company 2 Count. You can then either use logic on those two flags to work out how many calls are for both companies, or you could put the following preceding load on the calls table:

              Calls:
              LOAD
              *,
              if(Company1Count + Company2Count > 1, 1, 0) as BothCompaniesCount,
              if(Company1Count + Company2Count = 0, 1, 0) as NeitherCompaniesCount;
              LOAD
              [etc...]

               

              Hope that all makes some kind of sense. Post back here if you need any further pointers.

              Regards,
              Steve