Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I believe this expression should work:
=aggr(
concat({<Type={'MD'}>} distinct [Name], ' / ', [Name])
& ' - ' &
concat({<Type-={'MD'}>} distinct [Name], ' / ', [Name])
,[Account])
I believe this expression should work:
=aggr(
concat({<Type={'MD'}>} distinct [Name], ' / ', [Name])
& ' - ' &
concat({<Type-={'MD'}>} distinct [Name], ' / ', [Name])
,[Account])
in sorting section you can write expression
i.e.sort by expression
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
Another option
=Aggr(Concat(Aggr(Concat(DISTINCT Name, ' / '), Account, Type), ' - ', -Aggr(Rank(Type), Account, Type)), Account, Type)
=aggr(
concat({<Type={'MD'}>} distinct [Name], ' / ', [Name]) & ' - ' &
concat({<Type={'Nurse'}>} distinct [Name], ' / ', [Name])
,[Account])
Thanks everyone for all the great ideas. Marking this one correct because it was the easiest to understand.
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)
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.
Yup, thanks for catching that