Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Concat string by order

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

1 Solution

Accepted Solutions
Colin-Albert

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.

View solution in original post

7 Replies
anbu1984
Master III
Master III

Add RowNo() to your input

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

Try this expr in Text box

=Concat(Column2,'',RowNo)

Anonymous
Not applicable
Author

here is anbu's example

Thanks

BKC

Colin-Albert

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.

Not applicable
Author

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

Not applicable
Author

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

anbu1984
Master III
Master III

Check this app

Not applicable
Author

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