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

Variable creation for use in Where Clause

blunckc1 — Wed, 06 Jun 2018 07:26:45 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:42:38 GMT

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;

Re: Variable creation for use in Where Clause

olivierrobin — Wed, 06 Jun 2018 07:45:08 GMT

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)

Re: Variable creation for use in Where Clause

tamilarasu — Wed, 06 Jun 2018 08:32:27 GMT

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

Re: Variable creation for use in Where Clause

blunckc1 — Wed, 06 Jun 2018 22:34:21 GMT

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!

Re: Variable creation for use in Where Clause

tamilarasu — Fri, 08 Jun 2018 06:32:07 GMT

Great, Carl. I thought you wanted use variable in match function. So I posted the second solution. Happy Friday!