Variable creation for use in Where Clause

[blunckc1]

Hi world,

Need a hand creating a variable to use in a Where clause...  This is to replace this current where clause:

Where Match([Library Item Identifier],'cychs-bls-skills-assessment','cychs-immunisation-progam-nurse-update-2015','cychs-immunisation-program-nurse-assessment-2015','cychs-ipn-assessment-' (it goes for longer)

The items that are manually written in are all contained within a column from a table called ComponentMappingNull.  Here is the column name and the results within:

TQ_LibItem
cychs-bls-skills-assessment
cychs-immunisation-progam-nurse-update-2015
cychs-immunisation-program-nurse-assessment-2015
cychs-ipn-assessment-
cychs-ipn-assessment-2015
cychs-ipn-assessment-2015-2016__
cychs-ipn-assessment-2016
cychs-ipn-assessment-2018
cychs-medication-library-item-1yr-auto-assign-2018
cymhs-bls
cymhs-medication-assessment-community
lcch-medication-assessment-face-to-face
LCCH-pbls-paediatric-basic-life-support-assessment

So I essentially don't want to have to update the where clause script everytime a new value appears in the TQ_LibItem column from the ComponentMappingNull table like I do now.

How would this variable or script be written?  If that's at all possible...

Thanks in advance,

Carl

[tamilarasu · Accepted solution]

Hi Carl,

Did you try where exists function soemthing like.

ComponentMappingNull:

Load TQ_LibItem

From table;

T1:

Load *

from Source where exists( TQ_LibItem , [Library Item Identifier]);

Drop Table ComponentMappingNull;

[olivierrobin]

hello

try something like this :

load your column in a table

assign a variable with this expression

=chr(39) &concat(TQ_LibItem,chr(39) & ',' & chr(39)) & chr(39)

[tamilarasu]

Hi There,

If you are still looking for variable method, you can try this,

ComponentMappingNull:

Load Concat(Distinct Chr(39) & TQ_LibItem & Chr(39),',') as TQ_LibItem

From Table;

Let vTQ_LibItem = Peek('TQ_LibItem',0,'ComponentMappingNull');

DROP Table ComponentMappingNull;

T1:

Load *

From Source Where Match(TQ_LibItem , $(vTQ_LibItem));

[blunckc1]

Hi Tamil,

Sorry I overlooked your initial answer, this works!  Thank you.

My brain is struggling to comprehend that you can call a field name from a previous loaded table in a where clause in a later table without actually specifying which table that field belongs to.

But if it works, it works!

[tamilarasu]

Great, Carl. I thought you wanted use variable in match function. So I posted the second solution. Happy Friday!