they are different and this is not a join problem this is more a where clause problem and I would say probably most similar to a dynamic SQL but not quite as I need to get to a point to where I ferret out the column name and it's criteria within the same column. Even in a QV script, the where clause still needs x=y. I have x=y stored into a column so I can't just say where x. So I need to be able to extract it out of the data in the list table.
thank you for your response the key field(s) are stored in a column as rows in the list table and is the whole criteria. so per line it changes and it changes what columns are to be used in the "where clause".
this is my sample of how the list data looks. note that code_id, code_category, MFG, & GPI are in the code file not the list file and between the " quotes is the value of the criteria column in the list table.
for list_id 1 criteria could = "code_id = 1234"
for list _id 2 criteria could = "code category = 'X' and mfg = 'mfg01' "
for list _id 3 criteria could = "code category = 'X' and (mfg = 'mfg03' or mfg = mfg04)"
for list_id 4 cireria could = "code_id = "45634" and GPI =' R35'"
For each kind of join you will need in Qlik a key-field - if this kind of requirement is in (standard or extended) SQL possible you might better solve it there and just thranfer those output to qlik.
Nevertheless I could imagine to combine bothe tables with a kind of lookup() whereby it will probably quite hard to define the search-fields/values. Easier seems to me to try it within some kind of loop-logic whereby I wouldn't rather not try to slice the criteria to the same type of condition else the opposite. I mean something like this:
load * from List;
for i = 0 to noofrows('List')
let vWhere = peek('criteria', $(i), 'List');
peek('list id', $(i), 'List') as [list id],
peek('start date', $(i), 'List') as [start date],
peek('end date', $(i), 'List') as [end date],
peek('criteria', $(i), 'List') as [criteria]
from Code where $(vWhere);
You might need further a few string-manipulations to vWhere to strip the outside double-quotes, set the inside single-quotes correctly and to wrap fieldnames with spaces with square-brackets because fieldnames like code category are not valid. In this case it might be useful to adjust the fieldnames within table Code in a loadstep before and of course similar adaptions to the criteria field.
Of course this approach with looping several millions time over a table won't be very fast but with an incremental load approach it would only need once to run over all records.