7 Replies Latest reply: Mar 3, 2013 11:41 AM by Jason Michaelides RSS

    Grouping records by  a field from another table

    Paul Berendts

      I'm having trouble getting results grouped by the right field.

      I have two tables, transports and cars:

      Transports:
      Date Persons  Car
      1-1-2013 2 1
      1-2-2013 3 1
      1-3-2013 2 1
      1-4-2013 4 1
      1-5-2013 1 1
      1-1-2013 4 2
      1-2-2013 3 2
      1-3-2013 1 2
      1-4-2013 4 2
      1-5-2013 5 99

      Cars:
      Car Engine
      1 Euro5
      2 Euro4

      See that Car 99 is not in the cars file.
      What I want to calculate is the number of transport lines that are made with a Euro5, Euro4 and Euro3 engine:
      Cars which are not in the Cars file should be assumed to have an Euro5 engine:

      Engine Transports%
      Euro5 60%
      Euro4 40%

      I try to calculate this with a Grafiek (Chart?) with no dimensions, and an axpression:
      100*sum(if(Engine='Euro5',1,0))/count(Engine)

      This yields
      Engine Transports%
      Euro5 50%
      Euro4 50%

      Ia apparantly get only a line back for each line in Cars.

       

      Can anybody explain to me how to get the results I need?

        • Re: Grouping records by  a field from another table
          Jason Michaelides

          I think you have run into the classic QlikView challenges of dealing with NULLs. If you keep Transports and Cars as separate tables, then there cannot possibly be a link between them therefore it becomes extremely difficult to build an association with expressions.

           

          I can see 2 solutions with the limited information provided:

           

          1. Add extra records to the Cars table for all Cars that exist in the Transports table.
          2. Join the Transports and Cars tables together and replace NULLs with your desired values.

           

          Option two is probably easier.  It is done entirely in the script:

           

          //Create a single table for Transports and cars
          TransportsAndCars:
          LOAD * FROM Transports...;
          
          //Natural join to ensure you get all cars and all transports
          JOIN (TransportsAndCars)
          
          LOAD * FROM Cars...;
          
          //Create the map to use to replace NULL values
          Map_Nulls_Engines:
          LOAD
               Null()
               ,'Euro5'
          Autogenerate 1;
          
          MAP Engine USING Map_Nulls_Engines;
          
          //In order for the map to apply, you have to load the table again. You don't need this bit if you are performing a RESIDENT load with TransportsAndCars later in the script.
          
          RIGHT JOIN (TransportsAndCars) LOAD DISTINCT * RESIDENT TransportsAndCars;
          
          

           

          Now you have created the required Euro5 values in the data itself, the expressio0ns can remain simple.

           

          Hope this helps,

           

          Jason

          • Re: Grouping records by  a field from another table
            Paul Berendts

            Thanks for the input.

             

            Since I use MS-SQL as a source for the data, making a left join for the Transport and Cars table helped me do the calculation per transport record in stead of per Car.

             

            Using a when statement for the Engine field:

            when (engine is null) then 'Euro5 else engine as Engine2

            I could enter a default value for the cars that did not have a record in Cars.

             

            This gave me exactly the results I needed.

             

            Thanks.

            • Re: Grouping records by  a field from another table
              Paul Berendts

              Thanks for the input.

               

              Since I use MS-SQL as a source for the data, making a left join for the Transport and Cars table helped me do the calculation per transport record in stead of per Car.

               

              Using a when statement for the Engine field:

              when (engine is null) then 'Euro5 else engine as Engine2

              I could enter a default value for the cars that did not have a record in Cars.

               

              This gave me exactly the results I needed.

               

              Thanks.