2 Replies Latest reply: Aug 8, 2011 8:01 PM by jaymanson RSS

    Populating missing entries in table

      Hi all,

       

      I have an issue with how data is represented when joining two tables, mostly caused by the output from the database I'm reading which I have no way of changing. I think my script is going to have to be a little more involved than a basic join but I just can't figure it out.

       

      Say I have Locations:

       

       

      locationIDlocationName
      10001Location1
      10002Location2
      10003Location3


      and Items:

       

      itemCodelocationIDqty
      Item11000110
      Item1100025
      Item2100017
      Item2100032

       

      Now I just want one table with the information contained in it (for output to a qvd file). However, if I simply do this:

       

      RIGHT JOIN (Items) LOAD

           locationID,

           locationName

      RESIDENT Locations;

       

      DROP FIELD locationID FROM Items;

       

      I end up with this as my Items table:

       

      itemCodelocationNameqty
      Item1Location110
      Item1Location25
      Item2Location17
      Item2Location32

       

      However, what I want to end up with is an item entry for each valid Location, populated with a zero qty if none are listed in the imported table:

       

      itemCodelocationNameqty
      Item1Location110
      Item1Location25
      Item1Location30
      Item2Location17
      Item2Location20
      Item2Location32

       

      Any advice on how to code that in the script would be much appreciated. The source I'm reading will not output a zero qty line which would be the most obvious solution, but I'm just going to have to deal with that as I can't change it.

       

      Thanks in advance,

       

      Jay

        • Re: Populating missing entries in table
          John Witherspoon

          I think this would work.

           

          LEFT JOIN (Items)
          LOAD
          locationID as newLocationID
          ,locationName
          RESIDENT Locations
          ;
          NewItems:
          LOAD
          itemCode
          ,locationName
          ,if(locationID=newLocationID,qty,0) as qty
          RESIDENT Items
          ;
          DROP TABLES Items, Locations;

            • Re: Populating missing entries in table

              Thanks John, that didn't completely give the right result but that was the kick in the right direction I needed!

               

              One problem I still had was that each line was then creating a zero value entry so there was some duplicate lines; i.e. the table had item1, Location1, 0 and item1, Location1, 10...

               

              Rather than add more to the if statement, grouping the new table worked just fine for me:

               

              LEFT JOIN (Items)
              LOAD
              locationID as newLocationID
              ,locationName
              RESIDENT Locations
              ;
              NewItems:
              LOAD
              itemCode
              ,locationName
              ,Sum(if(locationID=newLocationID,qty,0)) as qty
              RESIDENT Items
               
              GROUP BY itemCode, locationName
              
              ;
              DROP TABLES Items, Locations;
              

               

              Thanks for the help