topic Re: Variable creation for use in Where Clause in QlikView

Variable creation for use in Where Clause

blunckc1 — Wed, 06 Jun 2018 07:41:05 GMT

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

Re: Variable creation for use in Where Clause

tamilarasu — Wed, 06 Jun 2018 07:52:12 GMT

Please have a look at the below link.

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

Re: Variable creation for use in Where Clause

YoussefBelloum — Wed, 06 Jun 2018 07:59:23 GMT

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

Re: Variable creation for use in Where Clause

blunckc1 — Wed, 06 Jun 2018 08:28:00 GMT

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

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

Re: Variable creation for use in Where Clause

YoussefBelloum — Wed, 06 Jun 2018 08:48:58 GMT

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