topic Re: Concat values of a field, twice, in calculated dimension in QlikView

Concat values of a field, twice, in calculated dimension

kdmarkee — Tue, 05 Jun 2018 20:40:53 GMT

I'm looking for some help with the Concat() function in a straight chart. I am trying to build a calculated dimension using the Concat() function where I assemble 2 Concat() statements in my calculated dimension, but I can't seem to get it to sort the way I want. I'm hoping what I want to do is possible in QlikView. Please see the attached qvw. Thanks.

Re: Concat values of a field, twice, in calculated dimension

Nicole-Smith — Tue, 05 Jun 2018 21:47:20 GMT

I believe this expression should work:

=aggr(

concat({<Type={'MD'}>} distinct [Name], ' / ', [Name])

& ' - ' &

concat({<Type-={'MD'}>} distinct [Name], ' / ', [Name])

,[Account])

Re: Concat values of a field, twice, in calculated dimension

poojashribanger — Wed, 06 Jun 2018 05:36:32 GMT

in sorting section you can write expression

i.e.sort by expression

Re: Concat values of a field, twice, in calculated dimension

Anonymous — Wed, 06 Jun 2018 10:04:50 GMT

HI Kris,

please use

Data:
load *,
if(Type='MD',1, if(Type='Nurse',2)) AS StaffFlag ;
LOAD * Inline
[
Account, Type, Name, Time
1, MD, Dr. Will, 10
1, Nurse, Kit, 5
1, MD, Dr. Sha, 12
1, Nurse, Chris, 6
]
;

and

the calculation dimension as =Aggr(Concat(Name,'/',StaffFlag),[Account])

Best Regards,
Ashok

Re: Concat values of a field, twice, in calculated dimension

sunny_talwar — Wed, 06 Jun 2018 11:52:16 GMT

Another option

=Aggr(Concat(Aggr(Concat(DISTINCT Name, ' / '), Account, Type), ' - ', -Aggr(Rank(Type), Account, Type)), Account, Type)

Re: Concat values of a field, twice, in calculated dimension

vvvvvvizard — Wed, 06 Jun 2018 12:10:05 GMT

=aggr(
concat({<Type={'MD'}>} distinct [Name], ' / ', [Name]) & ' - ' &
concat({<Type={'Nurse'}>} distinct [Name], ' / ', [Name])
,[Account])

Re: Concat values of a field, twice, in calculated dimension

kdmarkee — Wed, 06 Jun 2018 19:58:54 GMT

Thanks everyone for all the great ideas. Marking this one correct because it was the easiest to understand.

Re: Concat values of a field, twice, in calculated dimension

kdmarkee — Wed, 06 Jun 2018 20:01:09 GMT

Note, I believe there's just a copy/paste typo here as I believe Sunny meant to put this in the body of the response as this is the code that works in her qvw:

=Aggr(Concat(Aggr(Concat(DISTINCT Name, ' / '), Account, Type), ' - ', -Aggr(Rank(Type), Account, Type)), Account)

Re: Concat values of a field, twice, in calculated dimension

kdmarkee — Wed, 06 Jun 2018 20:02:23 GMT

Quick question on this on...why doesn't the sort weight sort on MD first and Nurse second by default because "m" comes before "n"? Or is that not the right way to look at sort weight? Thanks.

Re: Concat values of a field, twice, in calculated dimension

sunny_talwar — Wed, 06 Jun 2018 20:59:46 GMT

Yup, thanks for catching that

Re: Concat values of a field, twice, in calculated dimension

Anonymous — Thu, 07 Jun 2018 06:50:16 GMT

Per Qlikview Help:

concat([{set_expression}] [ distinct ] [ total [<fld {, fld}>]] expression[, delimiter[, sort_weight]])

Returns the aggregated string concatenation of all values of expression iterated over the chart dimension(s). 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.

Re: Concat values of a field, twice, in calculated dimension

kdmarkee — Thu, 07 Jun 2018 17:20:16 GMT

Ah, thanks for pointing that out...I missed it in the documentation.

Re: Concat values of a field, twice, in calculated dimension

sunny_talwar — Thu, 07 Jun 2018 17:37:42 GMT

as this is the code that works in her qvw:

You mean his, right?