Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kevin_creese
Contributor II
Contributor II

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

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?

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

4 Replies
sunny_talwar

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;

vishsaggi
Champion III
Champion III

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 ;

kevin_creese
Contributor II
Contributor II
Author

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!

sunny_talwar

Super awesome!!