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
Please have a look at the below link.
Hi,
Create your variable like this:
concat(chr(39) & TQ_LibItem & chr(39) ,',')
and on the where clause, put this:
WHERE MATCH(TQ_LibItem,'$(your_variable)')
Hey Youssef,
Thanks, nearly there. Variable (set vTQLibItems = concat(chr(39) & TQ_LibItem & chr(39) ,',');) looks as if it loads correctly when adding a text object with the text of =$(vTQLibItems).
But then the where clause doesn't return any data in the table it is being applied to:
Where ([Library Item Identifier] = '$(vTQLibItems)')
or
Where Match([Library Item Identifier], '$(vTQLibItems)')
The table the Where clause sits in is in a completely separate table.
TEACHQDataInput:
LOAD replace(ltrim(replace(Username,'0',' ')),' ','0') as PersonID,
Email,
[Library Item Identifier] as TQ_LibItem,
[Library Item],
Date(CompletedDate) as CompletedDate,
TimeStamp(TEACHQExtractTimeStamp) as TEACHQExtractTimeStamp,
ApplyMap('IPNPID',Username,'No') as IPN?
FROM
(qvd)
Where Match([Library Item Identifier], '$(vTQLibItems)');
My mistake..
I completely forgot that you should create your variable in the script to be able to use it on the where clause and so it's creation is a bit different on the script, you will need to use Peek() function, like this:
temp_table:
LOAD concat(chr(39) & TQ_LibItem & chr(39) ,',') as vTQLibItems
Resident the_table_where_you_have_vTQLibItems;
LET vTQLibItems = Peek('vTQLibItems');
DROP Table temp_table;
==> now you can use your variable the way described above