topic Re: select in field - search string - for multiple complex string values in QlikView

select in field - search string - for multiple complex string values

Mon, 19 Mar 2012 20:15:43 GMT

How do I pass below three string values in a 'Search String' text box of a 'Select in Field' Action?

01015 - BTR/LAFAYETTE, LA

01003 - SHREVEPORT, LA

01014 - NEW ORLEANS, LA

I read in QV help that masking of two strings (A|B) can be passed through search string. But this format doesn't seem to work for above three string values.

(01015 - BTR/LAFAYETTE, LA|01003 - SHREVEPORT, LA|01014 - NEW ORLEANS, LA) Any help wiill be appreciated.

I am using QV 9.0 SR7.

Re: select in field - search string - for multiple complex string values

Mon, 19 Mar 2012 21:55:18 GMT

Okay so I was able to figure something out, but its not the most elegant of solutions.

In the script all I loaded was an inline table with the values from above.

LOAD * Inline [

String

01015 - BTR/LAFAYETTE, LA

01003 - SHREVEPORT, LA

01014 - NEW ORLEANS, LA

];

Then I selected the field String.

Next we need to create a couple of variables(Settings->Variable Overview...->Add:

I made temp and temp2.

temp2 =trim(concat(String,'|'))

temp = '('&Replace(temp2, ' ', '?')&')'

Now create a text box ->Actions -> Add Select in Field For field String and Search String = temp.

This should do the trick when you click on the text object.

As far as I can tell the problem with the search string not working has to do with spaces, so what this does is replaces each space with a '?' which evaluates to any character.

I also attached the .qvw to show you a working example.

Hope this helps!

select in field - search string - for multiple complex string values

Tue, 20 Mar 2012 18:09:05 GMT

Its not just the white space that's a problem but its also any special characters involved in the string. I am using the following expression for my application

=if(num(%_Alignment)=2,

'('&replace(getfieldselections (CBSA_DESC, '|',1000),' ','?')&')' ,

'('&replace(getfieldselections (Level4_Display_New, '|',1000),' ','?')&')'

)

Where CBSA_DESC and Level4_Display_New are fields with numerous such strings. For example,

03022 - LUBBOCK, TX

03024 - DALLAS, TX (SE)

03023 - SAN ANTONIO, TX(SW)

03021 - CORPUS CHRISTI, TX (NE)

03014 - EL PASO/LAF, TX (N)