Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Kellerassel
Contributor III
Contributor III

Pass a string of comma separated values to a function as list of arguments


How can I turn a string with comma separated values stored in a variable into input arguments for a Match() function? 

And why doesn't it work this way?

table:
Load *
Inline [
Column
shoe
shirt
jacket
hat
];

Let vColumns = 'shoe'', ''jacket';

Trace '$(vColumns)';

NoConcatenate
filtered_table:
Load *
Resident table
Where Match(Column, '$(vColumns)') > 0
;

 For specific reasons I need '$(vColumns)' to be passed to Match() inside quotes.

Is there a way to write vColumns in a Let - Statement so it can be passed to Match() as '$(vColumns)'?

Labels (1)
1 Solution

Accepted Solutions
Kushal_Chawda

@Kellerassel  When you create a variable using LET it evaluates as expression and hence it is not recognising special characters as string. Instead you can create a variable using SET

SET vColumns = 'shoe','hat';

Trace '$(vColumns)';

NoConcatenate
filtered_table:
Load *
Resident table
Where Match(Column, $(vColumns))
;

View solution in original post

6 Replies
Kellerassel
Contributor III
Contributor III
Author

This doesn't work. And it seems to be copied straight out of ChatGPT.

Or
MVP
MVP

Your quotes and escapes are a little bit off, as you can see if you run the code in debug mode the variable returns "shoe', 'jacket" with double quotes around the string and single quotes after shoe and before jacket.

This should work:

table:
Load *
Inline [
Column
shoe
shirt
jacket
hat
];

Let vColumns = '''shoe'', ''jacket''';

Trace '$(vColumns)';

NoConcatenate
filtered_table:
Load *
Resident table
Where Match(Column, $(vColumns)) > 0
;tFacts:
Load * Inline [
StoreDate, SKU, StoreID, Value
10, A, 1, 100
10, A, 2, 110
10, B, 1, 300
17, A, 1, 110
17, B, 3, 200
24, A, 1, 110
24, C, 1, 1000
];

 

Kushal_Chawda

@Kellerassel  When you create a variable using LET it evaluates as expression and hence it is not recognising special characters as string. Instead you can create a variable using SET

SET vColumns = 'shoe','hat';

Trace '$(vColumns)';

NoConcatenate
filtered_table:
Load *
Resident table
Where Match(Column, $(vColumns))
;

anat
Master
Master

Set vColumns = 'shoe', 'jacket';

Use below condition

Where Match(Column, $(vColumns)) 
;

Kellerassel
Contributor III
Contributor III
Author

Special characters? Are you referring to escape characters? Why does an expression not recognize special characters? 

So, when I look at all the answers, including yours, I conclude that there is no way to express 

Let vColumns = 'shoe'', ''jacket'

in this way or any other way, either by escaping single quotes, or concatenating by using Chr(39), or using other quotes like " or [ or `

so that $(vColumns) can be passed to Match() in quotes?  

Kushal_Chawda

@Kellerassel  Yes I am referring to escape characters. When I say not recognising I mean to say it is not recognised as we type (because LET key words tries to evaluate it as expression which expecting some concatenation of string) we need to use  additional escape characters to recognise it the way we want it to.

Hence, to represent string in variable SET is the keyword