11 Replies Latest reply: Feb 27, 2015 6:00 PM by Dan Magnus RSS

    Inner Join Not Working

      I am tryinig to join these two datsets.  The script runs with no errors, getting 90K records from the 1st data set and 100K records from the 2nd data set.  However, if I try to create a straight table or table box there are no records showing...just the column/field names.  Where's the data?

       

       

       

      /*** GET DATA FROM ACCOUNT_BAN_TAXID ***/

       

       

      Load *,
      Account__c as AccountId;

      SQL SELECT Account__c,
      BAN__c,
      Id,
      Name,
      Tax_ID__c
      FROM Account_BAN_Tax_ID__c;

      Inner Join

      /*** GET DATA FROM ACCOUNT ***/
      Load *,
      Id as AccountId,
      Name as AccountName;

      SQL SELECT BANs__c,
      Name,
      Id,
      Tax_id_2__c,
      LastActivityDate,
      Total_Sims__c
      FROM Account;

      drop Field Name, Id;

       


      Thanks,   Dan

        • Re: Inner Join Not Working
          Massimo Grossi

          you're using an inner join

          if there isn't any common values

          you get 0 records

           

          example

           

          load * inline [

          field

          a

          b

          c

          ];

           

          inner join

          load * inline [

          field

          aa

          bb

          cc

          ];

          • Re: Inner Join Not Working
            Saradhi Balla

            the two accountid fields may have different values - could be the format or the value itself.

             

            before you do the inner join, check your Account__c and Id fields, their formats and values to do a comparison and to make sure you have the same values in the same format in both the fields. remember that Qlikview is case-sensitive.

            • Re: Inner Join Not Working
              Nick Hoff

              Load the data without the join and compare the two fields you are trying to join by in a table box field.   Find your differences and correct it.

                • Re: Inner Join Not Working

                  There are no differences.  The ID is the same in both tables.  When loaded without a join statement in the script Qlikview makes the join so I can see them in a chart and the join is done just fine.   However, I want the join in the script so I can export the data in one .txt file.


                    • Re: Inner Join Not Working
                      Nick Hoff

                      They are not being inner joined, they are being associated by a common field.  The inner join is going to create a new table from the other two tables only where your common fields match.  I couldn't imagine the association would work without creating a synthetic key since you have two fields with the same name. 

                       

                      Instead of using a inner join it sounds like you are more interested in concatenating the two tables together. Especially since both tables have the same table structure.  In the concatenate you can do a WHERE EXIST(Id,Id).  There wouldn't be a need to alias ID or name like you are doing.

                       

                      Also, when you mention the script runs preview your table in the table viewer.  Control T, and then preview the table and see what data is there.  If a field is missing from the preview then something is wrong with your formatting.

                  • Re: Inner Join Not Working
                    Stefan Wühl

                    Dan, when you expand the star symbols, you will get

                     

                    /*** GET DATA FROM ACCOUNT_BAN_TAXID ***/

                     

                     

                    Load

                         Account__c,

                         BAN__c,

                        Id,

                         Name,

                         Tax_ID__c,

                         Account__c as AccountId;

                    SQL SELECT Account__c,
                    BAN__c,
                    Id,
                    Name,
                    Tax_ID__c
                    FROM Account_BAN_Tax_ID__c;

                    Inner Join

                    /*** GET DATA FROM ACCOUNT ***/
                    Load

                         BANs__c,
                        Name,
                         Id,
                         Tax_id_2__c,
                         LastActivityDate,
                         Total_Sims__c,

                         Id as AccountId,
                         Name as AccountName;

                    SQL SELECT BANs__c,
                    Name,
                    Id,
                    Tax_id_2__c,
                    LastActivityDate,
                    Total_Sims__c
                    FROM Account;

                    drop Field Name, Id;


                    So your join is actually joining on three fields, Name, Id, and AccountId, right? Won't work, I believe, but don't know your data.


                    When you remove the join, the drop field statement will kick in at the end, so you get an association only by AccountId in that case.


                    Does this sound reasonable?