How to handle Input Box drop down when values have commas?

Fri, 14 Jun 2013 17:23:45 GMT

In a previous post Jeff Lancaster proposed a solution in case you want to have a drop-down menue in an input box and the values you are selecting from contain commas:

This is what I am using in the Constraints/Listed Values expression:

='None Selected,' & Concat(DISTINCT Replace(primpract_res_name,',','_'),',')

I also added 'None Selected' to the top of the list and forced a carriage-return after it, by including a comma. Since the field that I am using actually has commas in the field values themselves, I had to replace the comma with an underscore ("_"), then swap them back at the time of use.

I implemented the replacement with the underscore (which is not ideal because it results in showing the underscores in the drop-down menue) and am faced with the re-replacement at time of use. I tried the following, but I can see that the editor does not recognise the replacement()-function as a function (it's not turned blue). I wondered what is wrong with the syntax and how I can correct it. (FYI, I use this expression in the Background colour expression for a grid chart to set the colour of the bubbles.)


if([Var1]=Only({$<[Var2]={replace('$(Highlight)','_',',')}>} [Var1])  
   ,lightblue()
   ,lightred())

Note: Highlight is the Variable set using the drop-down menue in the Input Box, Var2 is the field that Highlight is compared against to limit the selection. Var1 is the Dimension that determines the x-axis in the grid chart.

Just correcting the code would be fine for me at this stage. Having a more elegant solution that allows to actually show comma values in the Input Box drop-down menue would be even better.

Thanks in advance

Tony

topic How to handle Input Box drop down when values have commas? in QlikView

How to handle Input Box drop down when values have commas?