topic Re: Create a string variable that can be used in WHERE clause from other tables in QlikView

Create a string variable that can be used in WHERE clause from other tables

kevin_creese — Fri, 10 Aug 2018 17:49:04 GMT

Hello Community!

This is probably an easy one for this group, but I'm struggling.

I have a list of items that I have assign to a variable in my load script. My plan is to use this list of items in the WHERE clause of other tables to limit the data that I'm returning so that I only return the items assigned to my variable.

Here is how I'm building my variable string:

Load holiday list into variable in script

// Create a temporary table

tmp:

NoConcatenate

Load concat(Distinct item_no,',') as FieldA

Resident <TABLE>;

let vSKU = peek('FieldA',0,'tmp');

drop table tmp;

This is yielding values like this: vSKU = ABCD-123,ABCF-456,ABFG-321

When I load my next table and try to use a WHERE clause that performs a MATCH(item_no_2,'$(vSKU)') > 0 I'm getting no results.

Based upon my testing, I know that I need to wrap my item numbers with a single quote (') so that they look like this:

vSKU = 'ABCD-123','ABCF-456','ABFG-321'

What function and I missing or misusing that will help me do this?

Re: Create a string variable that can be used in WHERE clause from other tables

sunny_talwar — Fri, 10 Aug 2018 17:59:27 GMT

Try this

// Create a temporary table

tmp:

NoConcatenate

Load Concat(DISTINCT Chr(39) & item_no & Chr(39),',') as FieldA

Resident <TABLE>;

LET vSKU = peek('FieldA',0,'tmp');

DROP Table tmp;

Re: Create a string variable that can be used in WHERE clause from other tables

vishsaggi — Fri, 10 Aug 2018 18:06:48 GMT

May be try below?

tmp:

NoConcatenate

Load concat(Distinct Chr(39)&item_no&Chr(39),',') as FieldA

Resident <TABLE>;

let vSKU = peek('FieldA',0,'tmp');

drop table tmp;

Then try like without Quotes.

WHERE Match(item_no_2, $(vSKU)) > 0 ;

Re: Create a string variable that can be used in WHERE clause from other tables

kevin_creese — Fri, 10 Aug 2018 18:08:31 GMT

Perfect! I had to modify my MATCH to MATCH(item_no_2,$(vSKU)) > 0 and then the stars aligned and I can get on with this project! Thanks Sunny!

Re: Create a string variable that can be used in WHERE clause from other tables

sunny_talwar — Fri, 10 Aug 2018 18:09:02 GMT

Super awesome!!