7 Replies Latest reply: Feb 21, 2012 3:54 AM by Miha Batic RSS

    Inner Join TableA.ColumnA to TableB.ColumnB where A is an integer contained in B

      Sorry if the subject is not particuarly clear, I have two tables (for purposes of illustration):

       

      id

      ---

      123456

      789101

      112131

      415161

      718192

       

      nameOfBatch

      ---------------------------------------

      SOMETHING_123456_SOMETHING

      OTHER_789101

      FOO_112131_BAR

      415161_BAR

      FOOBAR_718192_FOO

       

       

      I want to inner join these two tables, so i was thinking where nameOfBatch = '%id%' - it's imperfect but is good enough for the stats i'm after

       

      How would I express this join in Qlikview? I've tried:

       

      LogsEditedJob:

       

      LOAD

      id;

       

      SQL SELECT DISTINCT(id) FROM TableA

       

      LogsEditedJob2:

       

      INNER Join

      LOAD

      *;

       

      SQL SELECT nameOfBatch

                 FROM TableB

                 WHERE nameOfBatch LIKE '%' + id + '%'

      ;

       

       

      .. This throws an error saying 'id' can't be found - so i'm referencing it wrong?