Concat Issue

[kishorj1982]

Hello All,

I am selecting contains based on some specific conditions set as a part of button actions:  For that I am using Concat function :

='("' & concat(DISTINCT [Field], '"|"') & '")'

This works fine in all the cases. But I have certain field values with  -  "  character.  i.e. JT 6"   OR Long 4" 

in this case it wont work.

can you please help me out to get the solution ?

Thanks

[sunny_talwar]

I had a similar issue and I created a Field_New which did not had any special character in them and use that for button action

KeepChar(lower(Field), '1234567890abcdefghijklmnopqrstuvwxyz') as Field_New

and then ='("' & concat(DISTINCT Field_New, '"|"') & '")' as my action

[Colin-Albert]

It may be easier to use ascii codes for quotes rather than typing quotes in your expression.

     chr(34) is a double quote

     chr(39) is a single quote

The replace will substitute ^ for each " in your Field, do the concat, then convert ^ back to ".

Try this

='(' & chr(34) & replace(concat(DISTINCT replace([Field], chr(34), '^'),  chr(39) & '|' & chr(39)), '^', chr(34) ), & chr(34) & ')'

[kishorj1982]

Thanks Colin. It works - I have also tried out in the Text object. But when I am using this Concat output to make the corresponding selections in List box, it's not working.

Please let me know what's wrong.

[petter]

The inch symbol which is normally always represented by double quotes on a computer creates the problem. So you could replace it by either:

A) Two single quotes ''  - look how identical they look!   Here is " double quotes and here is '' single quotes

B) The REAL symbol for inch which is called double prime: ″

          Notice that it is slightly slanted and in unicode it is character code 8243. In QlikView you can use

          Chr(8243) to get it in an expression.

With option A you will be able to do normal searches for the user also including inches but they have to remember to use two single quotes.

With option B it is hard or almost impossible for interactive search including the double prime since it is not on any standard keyboard layout. However the users could use the wildcard ? instead of inch symbol (double prime).

Here is some test data where you can see how the double quote could be replaced by the two alternatives and try it out with the three fields T, Tdoublesingle and Tprime. Then you can test for yourself how compound searches work.

TestData:

LOAD

  T,

  Replace(T,Chr(34),Chr(39)&Chr(39)) AS Tdoublesingle

  Replace(T,Chr(34),Chr(8243)) AS Tprime

INLINE [

T

1.1" screws

3" nails

5.1" something

] ;

[kishorj1982]

Hello Petter,

Please find attached Sample ... Actually I want to copy selections of field FILE from Table to List box - both are in separate state. For first time selection I am using Copy from State. But once use select at least 1 value of File in State A to State B,  the CONCAT option comes in the picture from Second Button ( Text object) . This is basically to accumulate the selections made by user - multiple times.

Above tips I have used but I am not successful when " comes in the selections.

Please advice.

[petter]

Your inline table data is that the actual data that you get from somewhere? What is the source of this data - Excel sheets or databases or what?

[kishorj1982]

Its from Database.  I have just posted sample.

[petter]

And the text data I see in the INLINE is exactly like you get it from the database?

[kishorj1982]

Yes Petter.