2 Replies Latest reply: Oct 17, 2011 1:18 PM by Jean-Pierre Bakhache RSS

    adding to null, a value from another table

    Jean-Pierre Bakhache

      hi everyone,

       

      i have a table that contains values by month and category
      and another table which contains month and value

       

      it's not obligatory to have data for a certain category in all months

       

      i'd like to display in a chart all the months and for a certain category, the sum of the values from the 1st and 2nd table.
      this works fine when this category has a record by month in the 1st table, so if in the 2nd table there is a value for that month, it adds it up.
      but in case the category doesn't have a record in the first table for a specific month, it won't take into consideration the value for that month from the 2nd table.


      can someone find a solution for this without having to change in the script by adding a corresponding record in the 1st table for the missing category/month ?


      thx for ur help.

       

      i've attached a sample qvw to clarify my question: for example i want to display for category 'B' the value '10' in january (null from the 1st table and 10 from the 2nd).

        • adding to null, a value from another table
          Ali Hijazi

          add the following script to your original script

          tempCategory:

          LOAD Distinct Category as tempCategory

          Resident MonthCategory;

           

           

          join(tempCategory)

          LOAD Month as month Resident Months;

           

           

           

           

          inner join(tempCategory)

          LOAD Category as tempCategory

          Resident MonthCategory;

           

           

          inner join(MonthValue)

          LOAD tempCategory, month as Month Resident tempCategory;

          drop Table tempCategory;

           

           

          Concatenate(MonthCategory)

          LOAD

                    Month,

                    Value2 as Value,

                    tempCategory as Category

          Resident MonthValue;

          drop Table MonthValue;

           

           

           

          finally make your expression as sum(Value) only

            • adding to null, a value from another table
              Jean-Pierre Bakhache

              hey Ali,

               

              thanks for your answer. there's no doubt that it was a lot helpful as i got your point then changed a bit in the script to get the desired result.

               

              here's the final added script:

               

              tempCategory:

              LOAD Distinct Category as tempCategory
              Resident MonthCategory;

              Join

              LOAD Month as month
              Resident MonthValue;

              Join

              LOAD Month as month,
              Category as tempCategory,
              Value
              Resident MonthCategory;

              DROP Table MonthCategory;

              MonthCategory:
              LOAD month as Month,
              tempCategory as Category,
              Value
              Resident tempCategory;

              DROP Table tempCategory;

               

               

               

              As for the expression:

              if(Category = 'B',
              sum(Value) + sum(Value2)
              ,
              sum(Value)
              )