9 Replies Latest reply: Nov 5, 2017 2:13 PM by Shahzad Ahsan RSS

    How to Merge Table Data

    Shahzad Ahsan

      Hello Everyone

      I have problem in Qlik Sense

       

      I have 2 tables T1 & T2

      T1

      Date                      Flag

      01/Jan/2017               1

      02/Jan/2017               0

      03/Jan/2017               1

       

      T2

      Date                         Flag

      02/Jan/2017               1

      03/Jan/2017               2

      04/Jan/2017               4

       

      I want result Like this

      Date                         Flag

      01/Jan/2017               1

      02/Jan/2017               1

      03/Jan/2017               2   

      04/Jan/2017               4


      Means the dates that are in both T1 & T2 , show the data from T2 else from T1


      Please help me.


        • Re: How to Merge Table Data
          Massimo Grossi

          Start with T2 load, then load T1 with a not exists Date.

           

           

          T2:

          load * inline [

          Date,                         Flag

          02/Jan/2017 ,              1

          03/Jan/2017  ,             2

          04/Jan/2017   ,            4

          ];

           

          concatenate (T2)

          load * inline [

          Date           ,           Flag

          01/Jan/2017     ,          1

          02/Jan/2017      ,         0

          03/Jan/2017       ,        1

          ]

          Where not Exists (Date)

          ;

            • Re: How to Merge Table Data
              Shahzad Ahsan
              Hi Massimo

              It worked.

              Thanks a lot

              • Re: How to Merge Table Data
                Shahzad Ahsan

                This shows all rows from T2.

                I want all the rows from T1.

                So what can I do ??

                  • Re: How to Merge Table Data
                    Massimo Grossi

                    What's the result you want with T1 and T2 tables?

                      • Re: How to Merge Table Data
                        Shahzad Ahsan

                        Suppose T1 has 100 rows and T2 has only 20 rows.

                        I want all the 100 rows of T1. Those dates which are common in both, show their flag from T2. else show the flag from T1.

                        I want the result like this:

                        Date                         Flag

                        01/Jan/2017                    1

                        02/Jan/2017               1

                        03/Jan/2017               2  

                          • Re: How to Merge Table Data
                            Massimo Grossi

                            - load T1

                            - add the T2 records with a left join, so the result is a table with T1 records and 2 flag fields,

                            Flag for T1 and Flag2 for T2

                            - do a resident load of the T1 table; pick Flag2 if not null as your Flag Field, else pick Flag as your Flag Field

                             

                            T1:

                            load * inline [

                            Date           ,           Flag

                            01/Jan/2017     ,          1

                            02/Jan/2017      ,         0

                            03/Jan/2017       ,        1

                            ];

                             

                            //T2:

                            left Join (T1)

                            load Date, Flag as Flag2 inline [

                            Date,                         Flag

                            02/Jan/2017 ,              1

                            03/Jan/2017  ,             2

                            04/Jan/2017   ,            4

                            ];

                             

                            RENAME Table T1 to tmp;

                             

                            T1:

                            load

                            Date,

                            Alt(Flag2, Flag) as Flag

                            Resident tmp;

                             

                             

                            DROP Table tmp;

                              • Re: How to Merge Table Data
                                Shahzad Ahsan

                                I could not get this, I have never used resident.

                                It is showing error "tmp" table not found.

                                 

                                This is my last expression:

                                 

                                LOAD

                                    "Date",

                                    MainFlag

                                FROM [lib://Excel/T2.xlsx]

                                (ooxml, embedded labels, table is Sheet1);

                                 

                                concatenate 

                                 

                                LOAD

                                    "Date",

                                    Flag

                                FROM [lib://Excel/T1.xlsx]

                                (ooxml, embedded labels, table is Sheet1)

                                Where not Exists (Date);

                                  • Re: How to Merge Table Data
                                    Massimo Grossi

                                    for your second question

                                    Suppose T1 has 100 rows and T2 has only 20 rows.

                                    I want all the 100 rows of T1. Those dates which are common in both, show their flag from T2. else show the flag from T1.

                                    I want the result like this:

                                    Date                        Flag

                                    01/Jan/2017                    1

                                    02/Jan/2017              1

                                    03/Jan/2017              2 

                                    I didn't use a concatenate but a left join

                                    I added some comments


                                    T1:

                                    load * inline [

                                    Date          ,          Flag

                                    01/Jan/2017    ,          1

                                    02/Jan/2017      ,        0

                                    03/Jan/2017      ,        1

                                    ];

                                     

                                    //T2:

                                    left Join (T1)

                                    load Date, Flag as Flag2 inline [

                                    Date,                        Flag

                                    02/Jan/2017 ,              1

                                    03/Jan/2017  ,            2

                                    04/Jan/2017  ,            4

                                    ];

                                     

                                    RENAME Table T1 to tmp;               // rename the in memory table T1 to tmp

                                     

                                    T1:

                                    load

                                    Date,

                                    Alt(Flag2, Flag) as Flag

                                    Resident tmp;                                   // read from the in memory table tmp (resident = load from an in memory table),

                                                                                             // into the in memory table T1

                                     

                                    DROP Table tmp;