Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
blunckc1
Creator
Creator

Variable creation for use in Where Clause

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

4 Replies
tamilarasu
Champion
Champion

Please have a look at the below link.

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

YoussefBelloum
Champion
Champion

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
Creator
Creator
Author

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
Champion
Champion

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