How to Load/Select B.data where (B.ID Like (*IN(A.ID field)*))
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:
Load Project, ID
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)
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
Load Project, ID
load Concat (distinct 'id like '&chr(39)&'%'&ID&'%'&chr(39),' or ') as F1
let vLike = FieldValue('F1',1);
drop table Like;
SQL Select * from database_table_b where '$(vLike) ';
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.