How to Load/Select B.data where (B.ID Like (*IN(A.ID field)*))

[#1]

I'm fairly new to Qlikview.

I have a data table of ID's, and I need to SQL load another table, but only the rows where the ID is LIKE any of the ID's IN table A's ID field..

Ideally it would look something like this:

Table_A:

Load Project, ID

from some_table

Table_B:

SQL Select * from database_table_b where (id LIKE('%' & IN Table_A(ID)  & '%'))

I don't know if I can somehow combine the LIKE and IN. I have looked at wildmatch but it doesn't seem like what I need. Also, can I even send SQL statements related to local tables, or do I have to pull the whole table and then do the comparison locally into a new table, and then drop the huge table? (The database is managed using SQL server 2012)

Thanks!

[sll · Accepted solution]

Brian,

Have you looked at Joins, Keeps and Lookups? Here is a good document to get you going.

https://community.qlik.com/servlet/JiveServlet/previewBody/3412-102-4-3894/Joins%20and%20Lookups.pdf

Hope this helps.

Good luck.

Asim

[ramoncova06]

that is not possible to do, though you might be able to create a syntax for all your "likes" and then read it that way, though performance wise it might be just faster to bring the whole table in and then run your comparisons in QV

Table_A:

Load Project, ID

from some_table

Like:

load Concat (distinct 'id like '&chr(39)&'%'&ID&'%'&chr(39),' or ') as F1

Resident Table_A:

let vLike = FieldValue('F1',1);

drop table Like;

Table_B:

SQL Select * from database_table_b where '$(vLike) ';

[#4]

This wasn't what I thought I needed when I asked the question but in fact this will work best for my situation. My table1 has 50 program_id's, each with 6 PINS and 20 Prefix_id's, and I need to SQL load a tabe2 containing millions of records, but only keep the entries for table2 where the program_id, and a corresponding pin, and a corresponding prefix_id all match. this should narrow down table2 from millions to 100-200 thousand and each detail in table2 will correspond to a program in table1.

I am thinking I should do something like this:

inner keep (Table1) load * from (SQL select from table2data where...) ON (Table1.program_id=Table2.program_id and Table1.PIN=Table2.PIN and Table1.Prefix_id = Table2.Prefix_id)

This should remove all 3rd degree combinations from BOTH tables if there isn't a correspongind combination in the other table. Does this look right?

Thank you by the way for the post, it was quite helpful.

[#5]

This is exactly what I thought I needed, and it is actually a really cool way of manipulating the strings! Unfortunately for my situation it may be too complicated to implement.

Thanks for the time to help me, and I complement you for such a great solution!