7 Replies Latest reply: Dec 21, 2010 6:53 AM by Johann Choucq RSS

    CONCATENATE 2 tables after Join on Resident Table

    Johann Choucq

      Hello,

      Is it possible to Concatenate two tables which are the result of an INNER JOIN on Resident Table?

      Example :

      MaTable:

      LOAD
      Champ1,
      Champ2,
      RESIDENT MaPremiereTable;
      INNER JOIN LOAD Champ1, SUM(Indicateur1) AS Indicateur
      RESIDENT MaDeuxiemeTable
      GROUP BY Champ1;

      CONCATENATE


      LOAD
      Champ1,
      Champ2,
      RESIDENT MaPremiereTable;
      INNER JOIN LOAD Champ1, SUM(Indicateur2) AS Indicateur
      RESIDENT MaDeuxiemeTable
      GROUP BY Champ1;

      I would say that the CONCATENATE Order is not necessary, but in both cases, i don't get the result i am waiting for.

      The difference in the two load statement is the measure that i want to sum.

      The only answer i found is to load in two different tables, and then Concatenate them ....

      Thanks

      Johann

        • CONCATENATE 2 tables after Join on Resident Table
          John Witherspoon

          I believe the best approach is the one you already found - load them into two different tables, then concatenate the second onto the first when you're done, then drop the second.

            • CONCATENATE 2 tables after Join on Resident Table
              Johann Choucq

              Thank you for your answer John.

              Does it mean there is no way to do it like i try in my first example ?

              Because to concatenate afterwards, i need to execute a statement like :

              Load * Resident MaTable1;

              and the statement does nothing if i don't create a technical column ... what a pity ...

              Johann

                • CONCATENATE 2 tables after Join on Resident Table
                  Luis Laura

                  Hi,

                  Why difficult if we can make easy ?

                  1.- Tha table MaPremiereTable contains fields Champ1 & Champ2

                  2.- Creating the next table ...

                  SumIndicateurChamp1:

                  LOAD Champ1, SUM(Indicateur1) AS Indicateur1sum, SUM(Indicateur2) AS Indicateur2sum, SUM(Indicateur1+Indicateur2) AS IndicateurSum
                  RESIDENT MaDeuxiemeTable
                  GROUP BY Champ1;

                  You have automatic catenation (by columnar organization data) into tables & more facilities moreover clear.

                  Good luck, Luis

                    • CONCATENATE 2 tables after Join on Resident Table
                      Johann Choucq

                      In fact, i really don't understand why I can't use this statement.

                      MaTable:

                      LOAD
                      'Table1' AS Champ1,
                      Champ2,
                      RESIDENT MaPremiereTable;
                      INNER JOIN LOAD Champ1, SUM(Indicateur1) AS Indicateur
                      RESIDENT MaDeuxiemeTable
                      GROUP BY Champ1;

                      CONCATENATE

                      LOAD
                      'Table2' AS Champ1,
                      Champ2,
                      RESIDENT MaPremiereTable;
                      INNER JOIN LOAD Champ1, SUM(Indicateur2) AS Indicateur
                      RESIDENT MaDeuxiemeTable
                      GROUP BY Champ1;

                      When I execute the script, i don't get any errors, but i don't get a the result i am waiting for. For example, i don't get, in the column Champ1, the two differents string "Table1" and "Table2" but only "Table1" !?

                      Johann

                        • CONCATENATE 2 tables after Join on Resident Table
                          John Witherspoon

                          First, I think Luis has a good answer. The concatenation does seem pointless now that I'm looking at it more closely. Just put the sums in a different table as he indicated.

                          As for why you can't do it like you're doing it, just think about it step by step, as QlikView processes it. And let's create some sample date:

                          MaPremiereTable:
                          Champ1, Champ2
                          1, A
                          2, B

                          MaDeuxiemeTable:
                          Cham1, Indicateur1, Indicateur2
                          1, 1, 3
                          2, 2, 4

                          Step 1 - You build a table with fields Champ1 and Champ2.

                          MaTable:
                          Champ1, Champ2
                          1, A
                          2, B

                          Step 2 - You inner join to get field Indicateur

                          MaTable:
                          Champ1, Champ2, Indicateur
                          1, A, 1
                          2, B, 2

                          Step 3 - You concatenate the Champ1 and Champ 2 fields again:

                          MaTable:
                          Champ1, Champ2, Indicateur
                          1, A, 1
                          2, B, 2
                          1, A
                          2, B

                          Step 4 - You INNER join to Champ1=1,Indicateur=3 and Champ1=2,Indicateur=4. This eliminates the first two rows since they don't match the Indicateur values. Assuming I did all this right, you get only the second set of rows:

                          MaTable:
                          Champ1, Champ2, Indicateur
                          1, A, 3
                          2, B, 4

                          In some cases, you would get rows from the first load as well, but only where sum(Indicateur1) = sum(Indicateur2), I believe.

                          You seem to be thinking that QlikView will somehow process both inner joins independently. It doesn't. The last inner join affects ALL rows of the original table, not just the most-recently-concatenated rows. QlikView doesn't remember which ones you most recently concatenated, and doesn't care.

                      • CONCATENATE 2 tables after Join on Resident Table
                        John Witherspoon

                         


                        jchoucq wrote:Does it mean there is no way to do it like i try in my first example ?


                        Right.

                         


                        jchoucq wrote: Because to concatenate afterwards, i need to execute a statement like :
                        Load * Resident MaTable1;
                        and the statement does nothing if i don't create a technical column ... what a pity ...


                        Mmmm, no, you shouldn't have to create an extra column. You just have to tell it which table to concatenate to:

                        CONCATENATE (MaTable)
                        LOAD * RESIDENT MaTable1;
                        DROP TABLE MaTable1;

                        But again, I think Luis has a better answer.