3 Replies Latest reply: Jun 12, 2013 4:19 AM by Gustav Gnosspelius RSS

    Display unique values from two tables in one list

      I've got the following data (ProjectName is key):

       

      Table1:

      ProjectName

      Projectmanager_pr

      Days_pr

       

      Table2:

      ProjectName

      Projectmanager_ta

      Days_ta

       

       

      Now I wist to have a list showing all unique values from Projectmanager_pr & Projectmanager_ta. This so I can have a list of all the employees no matter if they are a Projectmanager_pr or Projectmanager_ta, then I can filter on their names and get their data no matter in what table they are listed.

       

      Sample data:

       

      Table 1

      ProjectName

      Projectmanager_pr

      Days_pr

      Project1Anna5
      Project2Erik10
      Project3Stefan15

       

      Table 2

      ProjectName 

      Projectmanager_ta

       

      Days_ta

      Project1Anna20
      Project2Anna30
      Project3Lars40

       

      Outcome

      Employees
      Anna
      Erik
      Stefan
      Lars

       

      Selecting for example Anna above I would get the below results in another object:

      ProjectNameDays_prDays_ta
      Project1520
      Project230

       

      Thanks in advance

        • Re: Display unique values from two tables in one list
          Gysbert Wassenaar

          Concatenate the two tables in the script so you have only one table:

           

          Table1:

          load

          ProjectName,

          Projectmanager_pr as Projectmanager,

          Days_pr as Days,

          'Pr' as Type

          from table1source;

           

          concatenate(Table1)

          load

          ProjectName,

          Projectmanager_ta as Projectmanager,

          Days_ta as Days,

          'Ta' as Type

          from table2source;

           

          Then in your table you can calculate the days for the different Types with expressions like this

          Days_pr: sum({<Type={'Pr'}>} Days)

          Days_ta: sum({<Type={'Ta'}>} Days)

          • Re: Display unique values from two tables in one list

            Thank you both for your answers! This was a simplified example, in reality there are 50 fields per table.

             

            Should I really join these big tables when I only want to join one field from each table?

            Will my key stay the same still?

             

            I just want a list of all the employees, they shouldn't be used as key.

             

            Sorry, I am new to QV, hope I don't use the wrong terminology.

            • Re: Display unique values from two tables in one list

              I managed to get it right by doing like this:

               

              Table1:

              LOAD

              ProjectName AS ProjectName,

              Projectmanager_pr AS Projectmanager_pr,

              Days_pr AS Days_pr

              FROM X;

               

              Table2:

              LOAD

              ProjectName AS ProjectName,

              Projectmanager_ta AS Projectmanager_ta,

              Days_ta AS Days_ta

              FROM X;

               

              Combinedtable:

              LOAD

              ProjectName AS ProjectName,

              Projectmanager_pr AS ProjectManager

              RESIDENT Table1;

               

              LOAD

              ProjectName AS ProjectName

              Projectmanager_ta AS ProjectManager

              RESIDENT Table2;