10 Replies Latest reply: Sep 24, 2018 4:08 PM by Rob Wunderlich RSS

    left join not working with resident tables

    Mikis De Witte

      Hello everyone,


      I have an issue in the data load editor, where I'm trying to left join Table2 on Table1.


      my code says:


      noconcatenate LOAD




      resident Table1;

      left join LOAD



      resident Table2;

      So I would expect a table with field1, field2, value, totalSample

      However, the totalSample is blank (it's added as a field, but it's blank).

      note that there are in fact a lot more fields and many values are blank, but I don't think this matters?

      I looked at the tables from every different angle, but I'm absolutely clueless

      When I export both tables to txt, drop both and then do the EXACT same load from the csv's, everything goes well and the totalSample is added correctly.

      This leads me to believe that it's not the method that is wrong, but something internal to qlik sense?

      Does anyone have a clue? Is it because I have so many blanks? Is it because there's a synthetic keys between the tables?

      Exporting resident tables, dropping them and uploading them again is slow but most of all sloppy. I would really like to know what causes the error.

      Thanks in advance,


        • Re: left join not working with resident tables
          Sunny Talwar

          The only reason I can think of is that no value of field1 from first table match with the field1 value from the second table. To check this, you can try to do a simple join (instead of Left Join) and see if there are totalSample which are not associated with any of the fields from first table. If this theory is true, you should be able to find what is causing the differences by selecting same field1's from both table and exporting them into Excel to see if they have extra spaces or if they are numbers, then may be there is some decimal mismatch.

          • Re: left join not working with resident tables
            Túlio Gonçalves

            Hi Mikis,


            Could you pls show the script where you load both Table 1 and Table  2 for the first time?


            best regards

              • Re: left join not working with resident tables
                Mikis De Witte

                Hello Tulio,


                I don't know how easy it is to understand this code for pro's, but it goes more or less like this



                NoConcatenate LOAD


                resident regionalData;

                outer join Load * FROM [lib://dataLoad/regionWeights.xlsx] (ooxml, embedded labels, table is weight);


                  mapping Load

                  field1 & '|'& field 2 as lookupKey,

                  weight as factWeight

                  FROM [lib://QliksenseDataLoad/regionWeights.xlsx] (ooxml, embedded labels, table is weight);


                NoConcatenate Load distinct



                  field1 & ' | ' & field2 as lookupKey,

                  ...some more fields,

                  resident Table1;



                NoConcatenate load *,ApplyMap('weight_mapping',lookupKey,null()) as factWeight

                resident Table2Temp;

                drop table Table2Temp;

                drop field lookupKey from Table2Temp_wWeight;




                ...some more fields,

                sum(factWeight) as total_factWeight

                resident regionalData_factWeight

                group By


                ...some more fields,



                Note that Table2 takes the sum over field2