Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
No sadly not, that would have made things too easy!
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.
It is, this is why I decided to use '%'+id+'%' rather than '%_' + id + '_%' - even without the underscores it's suitably unique for my purposes
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
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