topic Re: Order of concatination in QlikView

Order of concatination

Anonymous — Fri, 29 Dec 2017 12:13:27 GMT

Hi,

I have a function that creates a string based on the results of the adjacent field via concatination. But, the issue is that the order is not preserved at the end.

Using this formula :

=Concat(TOTAL <School, Class> Aggr(If(Excellent > Good, 'yes', 0), Year, School, Class), '|')

Output is like:

Normaly, the result should be "yes|0|yes", but its "0|yes|yes".

Is it possible to somehow resolve this issue?

updated formula due to wrong field existence

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 12:22:34 GMT

May be you can do this

=Concat(TOTAL <School, Class> Aggr(If(Excellent > Good, 'yes', 0), Year, School, Class), '|'), 'yes', Year)

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 12:23:38 GMT

The third parameter of Concat takes the Sort Order

Concat - script function ‒ QlikView‌

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 12:27:04 GMT

Great! That's something I needed. I updated the formula, so at the end it should be:

=Concat(TOTAL <School, Class> Aggr(If(Excellent > Good, 'yes', 0), Year, School, Class), '|',Year)

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 12:29:36 GMT

Are you trying to resolve this?

Comparison based on the group of fields

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 12:30:57 GMT

I have a similar issue

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 12:32:26 GMT

Oh okay...

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 12:34:21 GMT

It is really strange way of solving this with string functions, but still it works.

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 12:36:22 GMT

Yup, the only thing with that post is that there are gaps in the year. so, it goes from 2011, 2012 to 2014... so I am not sure if just concat will work... but yes, it is def. an option

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 12:49:29 GMT

stalwar1‌, I have one addition. What if the class field is conditional. Is it possible somehow to adapt the concatenation that in case of disabling the class field, it adapts for the <school, year>?

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 12:55:39 GMT

Conditional how?

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 13:04:46 GMT

Is it clear from the image?

Once disable class is clicked - the field class disappears. But, as you see from second table, where there is no class field, the concatenation still relies on the class field.

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 13:23:58 GMT

Can you share this qvw file... will make it easier for me to test out

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 13:29:37 GMT

updated file with buttons

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 13:33:42 GMT

Try this

=Concat(TOTAL <School, Class> Aggr(If(Excellent > Good, 'yes', 0), Year, School $(=if($(vShowClass)=0, ', Class', ''))), '', Year)

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 13:47:55 GMT

Perfectly fine )

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 15:04:44 GMT

Probably, I will bother you the whole day today. Upon some tests, I have encountered an issue. Here on the photo:

I realize that the issue is with sorting inside concat. When I change Year to Class, the subtotal for Class is correct, but then the order for the Year disappears.

Is it possible to somehow work around this issue?

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 15:18:35 GMT

So, it almost looks like you want double sort... first by Class and next by Year

=Concat(TOTAL <School, Class> Aggr(If(Excellent > Good, 'yes', 'no'), Year, School $(=if($(vShowClass)=0, ', Class', ''))), '|', Class + (Year/1E10))

Re: Order of concatination

Anonymous — Fri, 29 Dec 2017 15:24:51 GMT

What does the highlighted part mean: Class + (Year/1E10) ???

Re: Order of concatination

sunny_talwar — Fri, 29 Dec 2017 15:27:19 GMT

1e10 = 10000000000

So, in order to give it a lower sort weight, I am dividing Year by 10000000000