3 Replies Latest reply: Aug 4, 2017 2:27 PM by Sasidhar Parupudi RSS

    Combining two tables

    Camille Menten

      Dears,

       

      I have an exel file with different tables

      Table 1 with field 1.2.3.4

      Table 2 with field 1.2.5.6.7

       

      I want to create table 3 with fields 1.2.3.4.5.6.7

      where 5. is a new field

      where 1.2. is fed from both tables, and where relevant completed with 4. 6. or 7.

       

      but somehow it doesn't work and I don't see why not. Can somebody help?

       

      I created in Qlik Sense

      Table 1

      Load distinct

      1 as field 1,

      2 as field 2,

      3 as field 3,

      4 as field 4

       

      from abc

       

      Table 2

      Load distinct

      1 as field 1a,

      2 as field 2a,

      5 as field 5,

      6 as field 6,

      7 as field 7

       

      from def

       

      this because I could't create two tables with same fieldname

      Then I created a new section with

       

      Table 3

       

      Load *,

      field 1 as field 1b,

      field 2 as field 2b,

      3 as field 3,

      4 as field 4

       

      resident Table 1;

       

      concatenate (Table 3):

      Load *,

      field 1a as field 1b,

      field 2a as field  1b

      5 as field 5,

      6 as field 6,

      7 as field 7

       

      resident Table 2;

       

      drop table 1;

      drop table2;

       

      I don't know what I do wrong, but it does concatenate, but does not drop the tables giving me an end result combining all fields.

       

      Can you help me in the structure of my script?

       

      thanks a lot,

        • Re: Combining two tables
          Andrea Gigliotti

          maybe:

          drop tables [Table 1], [Table 2]  ?

          • Re: Combining two tables
            JeanPhilippe COUPE

            Hi,

             

            You must create a Link Table with key field (Field 1 - Field 2 and Field 1a - Field 2a).

             

            Link Table:

            Load Distinct

                 Field 1 as Field 1b,

                 Field 2 as Field 2b

            From Table 1;

             

            Concatenate(Link Table)

            Load Distinct

                 Field 1a as Field 1b,

                 Field 2a as Field 2b

            From Table 2;

             

            Then you left join (Link Table) From Table 1 et Table 2

             

            Left join (Link Table)

            Load *,

                 Field 1 as Field 1b,

                 Field 2 as Field 2b

            From Table 1;

             

            Left join (Link Table)

            Load *,

                 Field 1a as Field 1b,

                 Field 2a as Field 2b

            From Table 2;

             

            Drop Table Table 1, Table 2;

            • Re: Combining two tables
              Sasidhar Parupudi

              A solution would be

               

              Abc:
              Load
              F1&'|'&F2 as Key1,
              F3,
              F4;
              Load * inline
              [
              F1,F2,F3,F4
              ];

              Def:
              Load
              F1&'|'&F2 as Key1,
              F1,
              F2,
              F5,
              F6,
              F7;
              Load * inline
              [
              F1,F2,F5,F6,F7
              ];

              Left Join(Abc)
              Load Key1,
              F1,
              F2,
              F5,
              F6,
              F7
              Resident Def;

              Drop Table Def;
              drop Field Key1;