Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I was reading a document and came across the below statement. To give the context, i am pasting the whole statement. However I didnt understand the bolded part.
There are occasions when you want to pass a dynamic selection of values to a set statement. To do this I would need to add some single quotes to the string so that the CONCAT() function returns e.g. 'JKL','VWX'. But you cannot have the single quotes as they are, since they then would be interpreted when the Concat is evaluated instead of when the set expression is evaluated. Instead I use the Chr() function:
=CONCAT(Chr(39)&MyColumn&Chr(39),',')
I can then pass this concat statement to the inside of an expression…
=Sum({<MyColumn={$(=CONCAT(Chr(39)&MyColumn&Chr(39),','))}>} Value)
Please explain with a example.
Also why storing the expression as string in variable?
Eg- LET vGrossSales = 'FIELD={'&Chr(39) & 'Gross Sales' &Chr(39) & '}';
(1)
Chr(39) returns a single quote.
=CONCAT(Chr(39)&MyColumn&Chr(39),',') returns an output as 'JKL','VWX'.
if you dont use Chr(39), then
=CONCAT(MyColumn,',') returns an output as JKL,VXX.
So when you want to search a value in a field we enter as Field = 'JKL' .
So the output should be enclosed in single quotes and to get that output with single quote we use chr(39).
(2)
if you are using an expression in multiple places, its easy to store the expression in a variable and use that variable in all those multiple places.
If in need to modify the expression, it can be modified in just one place i.e the expression stored in the variable rather than going to multiple places and changing all the expressions .
Thanks
(1)
Chr(39) returns a single quote.
=CONCAT(Chr(39)&MyColumn&Chr(39),',') returns an output as 'JKL','VWX'.
if you dont use Chr(39), then
=CONCAT(MyColumn,',') returns an output as JKL,VXX.
So when you want to search a value in a field we enter as Field = 'JKL' .
So the output should be enclosed in single quotes and to get that output with single quote we use chr(39).
(2)
if you are using an expression in multiple places, its easy to store the expression in a variable and use that variable in all those multiple places.
If in need to modify the expression, it can be modified in just one place i.e the expression stored in the variable rather than going to multiple places and changing all the expressions .
Thanks
Thanks Chaitanya!!
I wanted to know, why chr(39) is used instead of writing the single cotes directly.
chr(39) refers to single quotes
Set analysis is very tricky ,so this is a work around it
In the expression, CONCAT(Chr(39)&MyColumn&Chr(39),',') , if you use single quote instead of CHR(39) the concat function treates 'MyColumn' as a value but when you write expression as CONCAT(Chr(39)&MyColumn&Chr(39),',') , the MyColumnis treated as a field inside the expression.