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]

Please have a look at the below link.

https://community.qlik.com/thread/303936#1497808

[YoussefBelloum]

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

[blunckc1]

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

[YoussefBelloum]

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