extracting criteria stored in column.

[#1]

I have two tables, list and code.

List has columns list id, start date, end date, criteria.

Code has code_id, GPI, code category, mfg.

I need to combine these tables to get the following columns, code.code_id, code.code_category, code.GPI, code.mfg, list.start date, list.end date in 1 result set.

The Join logic is stored in list.criteria as a full where clause and is dynamic and could be several combination of fields; if I was to copy and past the text out of the criteria column and paste into a where clause it would work just fine but I have a ~ 5 million records and is not possible to this manually.

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'"

how can I in QV query the code table and add the start and end date based on the data inside the criteria field?

The original data is stored in an oracle database if this helps.

[vkish16161]

Steven,

Joins in Qlik are very different than Joins in SQL.

Different Join Functions in Qlikview‌

QlikView Addict: Explaining Joins

[#3]

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.

[marcus_sommer]

I miss in your description a key-field between the tables. Only a where-clause isn't enough.

- Marcus

[#5]

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'"

[marcus_sommer]

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:

List:

load * from List;

for i = 0 to noofrows('List')

     let vWhere = peek('criteria', $(i), 'List');

     Match:

     load *,

          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);

next

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.

- Marcus