topic Re: Concat string by order in QlikView

Concat string by order

Fri, 10 Apr 2015 08:01:53 GMT

Hello,

I have a table with several values:

Column1 | Column 2

--------------|---------------

0733 | 0

0732 | 1

0730 | 1

0729 | 0

0728 | 15

I concatenate the values of Column 2 on a text object and I get the value: 001115.

The values seems to be concatenated by numerical order, but I would like to keep the order I have in the table: 011015

This is the expression I have in the text object:

=Concat(Column 2)

Any ideas how I can to this?

Thanks in advance.

Ivo

Re: Concat string by order

anbu1984 — Fri, 10 Apr 2015 08:14:09 GMT

Add RowNo() to your input

Load Column1 , Column2, RowNo() As RowNo From Table;

Try this expr in Text box

=Concat(Column2,'',RowNo)

Re: Concat string by order

Anonymous — Fri, 10 Apr 2015 08:18:52 GMT

here is anbu's example

Thanks

BKC

Re: Concat string by order

Colin-Albert — Fri, 10 Apr 2015 08:19:00 GMT

You can add a Sort-Weight parameter to the concat expression.

If the sort weight is not specified the concatenated data is sorted in expression order

concat ([ distinct ] expression [, delimiter [, sort-weight]])

Returns the aggregated string concatenation of all values of expression iterated over a number of records as defined by a group by clause. Each value may be separated by the string found in delimiter. The order of concatenation may be determined by sort-weight. Sort-weight should return a numeric value where the lowest value will render the item to be
sorted first. If the word distinct occurs before the expression, all duplicates will be disregarded.

Re: Concat string by order

Fri, 10 Apr 2015 09:39:23 GMT

Hello Colin.

The sorted value seems to work but still not in the order I want.

I added a set analysis to the expression because I need to limit my results:

Concat({<Column 3={'A'}>}Column 2, Column1)

This actually orders my results in a different way, but I think the set analysis in only affecting the first parameter of the concat function ([ distinct ] expression) and not the second (sort-weight]).

Any thoughts?

Ivo

Re: Concat string by order

Fri, 10 Apr 2015 09:48:46 GMT

Hi Ivo,

the sort-weight is numeric. So if you are using your column 2 as the sort, it will order according to smallest first, that is what you are seeing maybe? 0728,0729,0730,0732,0733 --> 15,0,1,1,0 ?

If you want to sort based on the rows in your original post, you'll need a field with that order, so maybe look at abnu's suggestion (or use one of the extra columns you may have in your model that has the right order already).

Joe

Re: Concat string by order

anbu1984 — Fri, 10 Apr 2015 11:12:20 GMT

Check this app

Re: Concat string by order

Fri, 10 Apr 2015 14:17:48 GMT

I had another parameters that were influencing the order of the results. So with the sort-weight the ordering works well.

One last thing: how can I invert a string, for example:

I have "Hello world" and would like to have "dlrow olleH".

Is there a function to achieve this?

Thank you and have a nice week end!

Ivo