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

[#1]

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?

[jagan]

Hi,

Is TableB has ID column, or is there any way to bring ID column In SQL.

If it is possible then

TableA:

LOAD

id;

SQL SELECT DISTINCT(id) FROM TableA

INNER Join

LOAD

*;

SQL SELECT nameOfBatch, id

           FROM TableB;

Regards,

Jagan.

[#3]

No sadly not, that would have made things too easy!

[jagan]

Hi,

Is this the exact BatchNames, each having different format, one has ID at the beginning, other has ID in between the words and other has at the end.  Each of them having different format. 

SOMETHING_123456_SOMETHING

OTHER_789101

FOO_112131_BAR

415161_BAR

FOOBAR_718192_FOO

Regards,

Jagan.

[#5]

It is, this is why I decided to use '%'+id+'%' rather than '%_' + id + '_%' - even without the underscores it's suitably unique for my purposes

[#6]

Hi, in QlikView I would do it like this:

t1:

LOAD * INLINE [

nameOfBatch
SOMETHING_123456_SOMETHING   
OTHER_789101   
FOO_112131_BAR
415161_BAR   
FOOBAR_718192_FOO
];

LJ_table:
LOAD * INLINE [
id
123456
789101  
112131
415161 
718192
];

// create Cross join (if there is not too much data)

Left Join (t1)
Load id as id2
Resident LJ_table;

// load just the ones that have id inside.
t2:
Noconcatenate Load
nameOfBatch as t2.nameOfBatch,
id2 as t2.id2
Resident t1
Where WildMatch(nameOfBatch, '*' & id2 & '*') > 0;

[#7]

Not bad, thanks for that - i'd love if there was a more efficient method anyone could recommend although with the size of my data this is viable for the moment

[#8]

With more and more data... you'll have to load the data in steps (look below). The seq field is just for help in which load we get the data... Substring counts number of '_', IsNum() differentiates if it's OTHER_789101 or 415161_BAR.
LOAD * INLINE [
nameOfBatch
SOMETHING_123456_SOMETHING   
OTHER_789101   
FOO_112131_BAR
415161_BAR   
FOOBAR_718192_FOO
];

t2_tmp:
NoConcatenate load
nameOfBatch,
subfield(nameOfBatch, '_', 1) AS id,
1 as seq
Resident t1
Where
SubStringCount(nameOfBatch, '_') = 1
And IsNum(subfield(nameOfBatch, '_', 1))
;

load
nameOfBatch,
subfield(nameOfBatch, '_',2) AS id,
2 as seq
Resident t1
Where
SubStringCount(nameOfBatch, '_') = 1
And IsNum(subfield(nameOfBatch, '_', 2));

load
nameOfBatch,
subfield(subfield(nameOfBatch, '_', 2), '_', 1) AS id,
3 as seq
Resident t1
Where
SubStringCount(nameOfBatch, '_') >= 2
;

drop table t1;

Left Join(t2_tmp)
LOAD * INLINE [
id, id_desc
123456, 1
789101, 2   
112131, 3  
415161, 4  
718192, 5
];

Regards,
Miha