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

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

Tue, 21 Feb 2012 07:56:58 GMT

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

---

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?

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

jagan — Tue, 21 Feb 2012 08:06:00 GMT

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.

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

Tue, 21 Feb 2012 08:07:04 GMT

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

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

jagan — Tue, 21 Feb 2012 08:13:31 GMT

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.

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

Tue, 21 Feb 2012 08:14:26 GMT

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

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

Tue, 21 Feb 2012 08:33:18 GMT

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;

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

Tue, 21 Feb 2012 08:36:52 GMT

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

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

Tue, 21 Feb 2012 08:54:23 GMT

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