6 Replies Latest reply: Dec 5, 2016 9:03 PM by Sunny Talwar RSS

    partial left join

    sebastien dupont

      Hi,

       

      I try to do something, but i don't know if it's possible.

      I need your help.

      My exemple is very simplistic, but in reality, my datas are much complexe and i must load them in the exemple order.

       

       

      First table : TABLE1

       

      CountryCity
      USAMiami
      FRANCEParis
      USADallas
      SPAINMadrid

       

      Second table join to the first table with concatenate (TABLE1)

      TABLE2

      citylanguage
      LyonFrench
      New YorkEnglish

       

      Result :

       

      TABLE1

      CountryCityLanguage
      USAMiami
      FRANCEParis
      USADallas
      SPAINMadrid
      LyonFrench
      New YorkEnglish

       

      The third table to complete partial column Country

       

      TABLE 3

      CountryCity
      FRANCELyon
      USANew York
      USAAtlanta
      USALos Angeles

       

      Expected résult with a left join

       

      TABLE1

       

      CountryCityLanguage
      USAMiami
      FRANCEParis
      USADallas
      SPAINMadrid
      FRANCELyonFrench
      USANew YorkEnglish

       

      In sql, i should do something with where clause : where table1.country is null or in join statement table1.country=table3.country and table1.country is null.

       

      How to do something similar in Qlikview ?

        • Re: partial left join
          sebastien dupont

          I have an issue by adding a new field in left join, drop the older and rename new field.

           

          Left join (Table1)

          city,

          Country as Country_temp

          From Table3;

          Drop field Country from Table1;

          Rename Country_temp to Country;

           

          If you have another solution !?!

          • Re: partial left join
            Sunny Talwar

            May be using ApplyMap with Mapping load

             

            TABLE3:

            Mapping

            LOAD City,

              Country;

            LOAD * INLINE [

                Country, City

                FRANCE, Lyon

                USA, New York

                USA, Atlanta

                USA, Los Angeles

            ];

             

            TABLE1:

            LOAD * INLINE [

                Country, City

                USA, Miami

                FRANCE, Paris

                USA, Dallas

                SPAIN, Madrid

            ];

             

            Concatenate(TABLE1)

            LOAD * INLINE [

                City, Language

                Lyon, French

                New York, English

            ];

             

            FinalTable:

            NoConcatenate

            LOAD If(Len(Trim(Country)) = 0, ApplyMap('TABLE3', City), Country) as Country,

              City,

              Language

            Resident TABLE1;

             

            DROP Table TABLE1;

             

            Capture.PNG