Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

JustinDallas
Valued Contributor II

Variable Expansion in Match

Hello Folks,

I've got a small variable expansion in a Match statement that isn't working like I think it should work.  It looks something like this.

SET vCompanies = 'McDonalds,BurgerKing'

;

Menu:

LOAD * WHERE

MATCH(Restaraunt, '$(vCompanies)')

;

LOAD * Inline

[

'Id', 'Restaraunt', 'Food'

    1, 'McDonalds', 'Broken McFlurry Machine'

    2, 'McDonalds', 'Big Mac'

    3, 'BurgerKing', 'Whopper'

    4, 'BurgerKing', 'Whopper Jr.'

    5, 'Wendys', 'Frosty'

]

;

The issue,  is that the vCompanies variable isn't being split and put into the Match statement.  I expected, maybe naively, that my variable would be expanded and all would be well.

Any help on getting my list into a variable and into a MATCH statement is greatly appreciated.

1 Solution

Accepted Solutions
JustinDallas
Valued Contributor II

Re: Variable Expansion in Match

This was my solution.

SET vBillTos = 'NAPCAY','GENCHA03';

....

OrderHeader:

LOAD

....

WHERE Match(ord_billto, $(vBillTos))

Notice how there are no single or double quotes around the vBillTos expansion term.

3 Replies
fosuzuki
Valued Contributor II

Re: Variable Expansion in Match

TRY LIKE THIS:

SET vCompanies = 'McDonalds','BurgerKing';

Menu:

LOAD * WHERE

MATCH(Restaraunt, $(vCompanies))

;

LOAD * Inline

[

'Id', 'Restaraunt', 'Food'

    1, 'McDonalds', 'Broken McFlurry Machine'

    2, 'McDonalds', 'Big Mac'

    3, 'BurgerKing', 'Whopper'

    4, 'BurgerKing', 'Whopper Jr.'

    5, 'Wendys', 'Frosty'

]

;

MVP
MVP

Re: Variable Expansion in Match

Use something like this..

SET vCompanies = 'McDonalds','BurgerKing'; 


Menu: 

LOAD

*

WHERE  

MATCH(Restaraunt, $(vCompanies)); 

LOAD * Inline 

Id, Restaraunt, Food 

    1, McDonalds, Broken McFlurry Machine 

    2, McDonalds, Big Mac 

    3, BurgerKing, Whopper 

    4, BurgerKing, Whopper Jr. 

    5, Wendys, Frosty 

;

Or use something like below.

Temp:

Load * Inline

[

Company

McDonalds

BurgerKing

];


Menu: 

LOAD * Inline 

Id, Restaraunt, Food 

    1, McDonalds, Broken McFlurry Machine 

    2, McDonalds, Big Mac 

    3, BurgerKing, Whopper 

    4, BurgerKing, Whopper Jr. 

    5, Wendys, Frosty 

]Where Exists(Company,Restaraunt);


Drop Table Temp;

JustinDallas
Valued Contributor II

Re: Variable Expansion in Match

This was my solution.

SET vBillTos = 'NAPCAY','GENCHA03';

....

OrderHeader:

LOAD

....

WHERE Match(ord_billto, $(vBillTos))

Notice how there are no single or double quotes around the vBillTos expansion term.

Community Browser