12 Replies Latest reply: Aug 28, 2015 2:26 PM by Sasidhar Parupudi RSS

    Join not working corectly

      I have trouble joining two different tables. Have searched through community for answers with no results. I do believe that i am missing something.

       

      If i am trying to join some data just inline in script it works perfectly:

      TEST:

      Load * Inline [ 

      ID,NAME1,SURNAME1,REGION,COUNTRY,NUMBER,T1

      1,a,aa,EU,UK,323,T1

      2,b,bb,EU,UK,454,T1

      1,a,aa,EU,UK,323,T1

      2,b,bb,EU,UK,454,T1

      ];

       

      Join(TEST)

      LOAD * Inline [

      ID,NAME2,SURNAME2,REGION,COUNTRY,NUMBER,T2

      1,a,aa,EU,UK,323,T2

      2,b,bb,EU,UK,454,T2

      1,a,aa,EU,UK,323,T2

      2,b,bb,EU,UK,454,T2

      ];

       

      And i am getting result which i do want to get:

      IDNAME1SURNAME1NAME2SURNAME2REGIONCOUNTRYNUMBERT1 T2
      1aaaaaaEUUK323T1T2
      2bbbbbbEUUK454T1T2

       

      I do want to create a table with unique combinations from those 2 tables and it works perfectly with Inline.

       

      However, i am loading data from xlsx and the result is not what i have expected

      IDNAME1SURNAME1NAME2SURNAME2REGIONCOUNTRYNUMBERT1T2
      1aaaEUUK323T1
      1aaaEUUK232T2
      2bbbEUUK454T1
      2bbbEUUKJ454T2

       

      Here is a simple script that i have:

      TABLE:

      LOAD

           ID, //can repeat

           NAME1,

           SURNAME1,

           REGION,

           COUNTRY,

           NUMBER,

             'T1' as T1 //field for checking if joined correctly

      FROM xlsx file

       

      Join(TABLE)

      LOAD

           ID, //can repeat

           NAME2,

           SURNAME2,

           REGION,

           COUNTRY,

           NUMBER,

             'T2' as T2 //field for checking if joined correctly

      FROM other xlsx file

       

      There is no unique IDs in both tables from xlsx.

      There are some lines that matches in both xlsx's.

       

      I ma just trying to find some hidden hint which i have missed.

      Help is very appreciated.

        • Re: Join not working corectly
          Alessandro Saccone

          Try with:

           

          TABLE:

          LOAD

               ID, //can repeat

               NAME1,

               SURNAME1,

               REGION,

               COUNTRY,

               NUMBER,

                 'T1' as T1 //field for checking if joined correctly

          FROM xlsx file

           

          Join(TABLE)

          LOAD

               ID, //can repeat

               NAME2,

               SURNAME2,

               REGION as REGION2,

               COUNTRY as COUNTRY2,

               NUMBER as NUMBER2,

                 'T2' as T2 //field for checking if joined correctly

          FROM other xlsx file

           

          let me know

          • Re: Join not working corectly
            Jonathan Dienst

            Firstly, the default join in QV is a full outer join.

            Secondly, Your join key is NOT ID - it is a composite of ID, REGION, COUNTRY and NUMBER.

            Thirdly - no rows in Table 2 match any rows in Table 1 for this composite key

            So the rows from Table2 are effectively being concatenated onto Table1.

             

            • Perhaps REGION, COUNTRY and NUMBER should not be part of the join key? If you want to keep them, you will need to alias the field names like you did with NAME1/NAME2.
            • Or do not load them if you do not need these extra fields.
            • Or look for a data set where there are composite keys in both tables
            • Re: Join not working corectly
              Sasidhar Parupudi

              Try

               

              QUALIFY REGION,COUNTRY;

              TABLE:

              LOAD

                   ID, //can repeat

                   NAME1,

                   SURNAME1,

                   REGION,

                   COUNTRY,

                   NUMBER,

                     'T1' as T1 //field for checking if joined correctly

              FROM xlsx file

               

              Join(TABLE)

              LOAD

                   ID, //can repeat

                   NAME2,

                   SURNAME2,

                   REGION,

                   COUNTRY,

                   NUMBER,

                     'T2' as T2 //field for checking if joined correctly

              FROM other xlsx file

               

              hth

              Sasi

              • Re: Join not working corectly
                Massimo Grossi

                are you sure the result from inline is what you want?

                in table viewer the result using your script (with inline) is 8 records

                1.png

                 

                if you load from inline or from excel or from ..... the same data you get the same result

                • Re: Join not working corectly
                  Tobias Klett

                  Hi Danas,

                   

                  your join seperates the lines because the kombinations are not the same.

                  Join_Error.png

                  Hope this helps

                   

                  Regards Tobias

                    • Re: Join not working corectly

                      Thanks Tobias for noticing, that was my typing mistake, the field you have showed should be the same.

                       

                      Thanks Alessandro for suggestion. However, this information is loaded from Sales opportunities and Purchase opportunities. Name and Surname can differ due to typing by users. There is a master file which contains IDs attached to Name and surname. How ever Region, Country and Number is not available for mistake information. I want to have a common information added to the same table from several tables where Name and Surname can differ, other information will be exact the same. As for ID it can be attached to many combinations of Region, Country and number.

                       

                      During my experiments i have found if i am removing Number from Load information is joined correctly. Number field in database contains Letter, numbers and symbols like "/,-,\" etc.

                       

                      Thank Jonathan for explanation. I still do not have a full scope of final result that i am trying to reach. But for time being it seems for me that this is a good way regarding the databases and information i deal with. At the moment it seems that the number field is messing with my Join somehow. I really appreciate your suggestions.

                       

                      Let me get back on other suggestions a bit later.

                    • Re: Join not working corectly

                      I have found that if not adding "number" into load, Join works perfectly.

                       

                      Join with "NUMBER"

                              

                      IDNAME1SURNAME1NAME2SURNAME2REGIONCOUNTRYNUMBER
                      2446STE1REC1EUUKP-13/456/12345
                      2446STE2REC2EUUKP-13/456/12345
                      2447STE1REC1EUUKP-13/456/12346
                      2447STE2REC2EUUKP-13/456/12346
                      2464AQU1SMI1EULTP-13/456/12347
                      2464AQU2SMI2EULTP-13/456/12347
                      2465AQU1SMI1EULTP-13/456/12348
                      2465AQU2SMI2EULTP-13/456/12348
                      2469AQU1SMI1EULTP-13/456/12349
                      2469AQU2SMI2EULTP-13/456/12349

                       

                      Joint not loading "Number":      

                      IDNAME1SURNAME1NAME2SURNAME2REGIONCOUNTRY
                      2446STE1REC1STE2REC2EUUK
                      2447STE1REC1STE2REC2EUUK
                      2464AQU1SMI1AQU2SMI2EULT
                      2465AQU1SMI1AQU2SMI2EULT
                      2469AQU1SMI1AQU2SMI2EULT

                       

                      The Question is, maybe there is some issues with data field properties? I am not good in Qlikview, so trying to find any possibilities.

                       

                      Regarding QUALIFY, i will read a bit more about this function before answering.

                      I need to understand how to use it.

                        • Re: Join not working corectly
                          neetha P

                          Hi Danas,

                          Please post sample data,so can help.

                          Regards

                          Neetha

                            • Re: Join not working corectly

                              I believe i have replicated issue on the small scale.

                              Here is the data

                              Table1      

                              IDNAME1SURNAME1REGIONCOUNTRYNUMBER
                              2446STERRECKEUUK
                              2447STERRECKEUUK
                              2469AQUASMITEULT
                              2464AQUASMITEULT
                              2465AQUASMITEULT
                              2286OSTEVITAEUUK
                              2496NATRSERVEUIE
                              2576OSTRPHAREU
                              2628COLGEUUK
                              704LOCOASTEEUUKPL0166/0058
                              704LOCOASTEEUUKPL0166/0058
                              2023BEECGLAXEUUKPL00079/0280
                              2026BETNGLAXEUUKPL10949/0014
                              2024BEECGLAXEUUKPL00079/0386
                              2025BEECGLAXEUUKPL00079/0385

                               

                              Table2      

                              IDNAME2SURNAME2REGIONCOUNTRYNUMBER
                              206KETO(LEKEUMD
                              206KETO(LEKEUUA
                              228PALI(LEKEUMD
                              228PALI(LEKEUUA
                              319TROX(BALEUMD
                              329LINE(LEKEUMDPL0166/0058
                              329LINE(LEKEUUA
                              363PERS(SANEUMD
                              363PERS(SANEUUA
                              873PERS(SANEUMD
                              873PERS(SANEUUA
                              1005DOXYEURO
                              1005DOXYEURO
                              2446STERRECKEUUK
                              2447STERRECKEUUK
                              2464AQUASMITEULT
                              2465AQUASMITEULT
                              2469AQUASMITEULT

                               

                              The script that i am using:

                              OPP:

                              LOAD

                                ID & '-' & REGION & '-' & COUNTRY & '-' & NUMBER as PREKE.ID,

                                ID,

                                NAME1,

                                SURNAME1,

                                REGION,

                                COUNTRY,

                                NUMBER

                              FROM table1.xlsx

                               

                              Join(OPP)

                              LOAD

                                ID & '-' & REGION & '-' & COUNTRY & '-' & NUMBER as PREKE.ID,

                                ID,

                                NAME2,

                                SURNAME2,

                                REGION,

                                COUNTRY,

                                NUMBER

                              FROM table2.xlsx

                          • Re: Join not working corectly

                            With my skills in Qlikview, QUALIFY does not help solve the issue.

                            I would really appreciate if somebody can help with this.

                             

                            I have an idea that Join is not working if there is several lines that are matching.