Concat values of a field, twice, in calculated dimension

[kdmarkee]

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.

[Nicole-Smith · Accepted solution]

I believe this expression should work:

=aggr(

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

& ' - ' &

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

,[Account])

[poojashribanger]

in sorting section you can write expression

i.e.sort by expression

[Anonymous]

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

[sunny_talwar]

Another option

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

[vvvvvvizard]

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

[kdmarkee]

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

[kdmarkee]

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)

[kdmarkee]

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.

[sunny_talwar]

Yup, thanks for catching that