Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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)
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));
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!
Great, Carl. I thought you wanted use variable in match function. So I posted the second solution. Happy Friday!