topic Re: Concat Issue in QlikView

Concat Issue

kishorj1982 — Wed, 02 Mar 2016 15:40:15 GMT

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

Re: Concat Issue

sunny_talwar — Wed, 02 Mar 2016 15:47:41 GMT

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

Re: Concat Issue

Colin-Albert — Wed, 02 Mar 2016 15:55:26 GMT

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) & ')'

Re: Concat Issue

kishorj1982 — Wed, 02 Mar 2016 19:01:33 GMT

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.

Re: Concat Issue

petter — Wed, 02 Mar 2016 19:27:29 GMT

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

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

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

INLINE [

1.1" screws

3" nails

5.1" something

] ;

Re: Concat Issue

kishorj1982 — Wed, 02 Mar 2016 20:12:37 GMT

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.

Re: Concat Issue

petter — Wed, 02 Mar 2016 20:50:20 GMT

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?

Re: Concat Issue

kishorj1982 — Wed, 02 Mar 2016 20:52:16 GMT

Its from Database. I have just posted sample.

Re: Concat Issue

petter — Wed, 02 Mar 2016 20:53:40 GMT

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

Re: Concat Issue

kishorj1982 — Wed, 02 Mar 2016 20:54:45 GMT

Yes Petter.

Re: Concat Issue

petter — Wed, 02 Mar 2016 21:02:30 GMT

This data is really messy. It is also double quotes that doesn't mean inches in there and a lot of single quotes too. You need to wash your data and it is more complicated than just a few lines of load script. For instance should the double quotes that means inches be preceded by numbers and if not they should be replaced by something else so you have a more complicated replace pattern...

Re: Concat Issue

sunny_talwar — Wed, 02 Mar 2016 21:08:42 GMT

Not sure how this is getting used, but won't the attached work for you?

Re: Concat Issue

petter — Wed, 02 Mar 2016 21:17:40 GMT

Looking closer at your data it seems like this comes from a system that does it's own kind of quoting logic. So it seems like starting ending single quotes should be removed. And likewise starting and ending double quotes should be removed. And finally all double double-qutoes should be single doubles quotes. Only then can you apply the logic I explained to you...

Re: Concat Issue

kishorj1982 — Wed, 02 Mar 2016 22:05:44 GMT

Hi Sunny - Basically we dont want to change the look and fill of the original Field FILE. We just want to copy the Field from one state to another as it is. Please suggest

Thanks

Re: Concat Issue

swuehl — Wed, 02 Mar 2016 22:12:57 GMT

Maybe like attached, getting rid of the compound search?

Re: Concat Issue

sunny_talwar — Wed, 02 Mar 2016 22:27:38 GMT

Not sure what the reluctance is. On the front end you would still be working with the FILE field. The only time you would use FILE_NEW is to do selections and copying things over etc (which won't even be visible to the users).

I am not saying this is the best way and knowing Petter and Stefan, I am sure they will come up with much better ways to do it, but I think you can definitely explore the option I proposed and test it out a little more.

Re: Concat Issue

kishorj1982 — Thu, 03 Mar 2016 00:19:56 GMT

Hi swuehl‌

Once again you NAILED it. Your expression looks pretty simple and straightforward and moreover it has doesnt required the Data Manipulation.

Thanks a Ton. Please suggest me how you develop the approach - what we should do extra to ramp up in terms of Qlik experties?

Hi Sunny - Definitely I will keep this in mind as well. Even if I am not using it now, its good to know the approach you suggested.

Hello - Colin and Petter,

Your suggestions are also valuable. Thanks again.

Re: Concat Issue

kishorj1982 — Thu, 03 Mar 2016 01:08:01 GMT

Hi,

I have one more query - The Selections are now as per expectations from one state to another. But my Table chart is dynamic and based on the SLIDER object the expressions are controlled and so that the dimensions. I.e. if I have total 10 rows in the Table originally but my slider is set such a way that only 5 are visible. Hence only 5 dimensions are visible. Now if I am saying select All - its selecting all 10 Dimensions which doesnt make sense. If my slider allow me to see only 5 dimensions, then Select all should select only 5 dimensions.

Can you please suggest how we can pass the Dynamically controlled expressions(and hence dimensions) from 1 state to another?

Thanks

Re: Concat Issue

kishorj1982 — Wed, 06 Apr 2016 17:24:36 GMT

HI Stefen.

last time you have provided the solution to the issue above where I wanted to add and append selected fields from one state to another. I have worked on the same lines to remove the selection from the another state with diff button action

It works fine in my application.

But it shows formula in the current selections instead of the actual selections. To get rid off it I have added one more button action - "Select Possible" IN the intended field. But its not giving correct results when I am removing selections from another button.

Can you please let me know how to display the actual selections in the current selections box rather than Formula...that too without using the SELECT POSSIBLE additional action ?

Attached is the sample accplication