Discussion Board for collaboration related to QlikView App Development.
I have a trouble in using sort-weight in QlikView Concat function in pivot table expression.Input:
Dimension - ID, Code
Expressions - Item, Strength
for some reason, this isn't working.
Concat(Strength, ',', Aggr(Rank(REC_Num),Code,Item))
The order of concat should be same in Item and Strength expressions.
Thank you so much for your response. I appreciate it
I thought I will give a try in chart function but it didn't work.
Also, there is no guarantee that Strength column is always alphanumeric. It could be just string of alphabets.
So, I created a as sort key(autonumber) in script and used it in concat chart function.
LOAD * INLINE [
ID, Code, Item, Strength, RecNum
ID1, AA11, Item1, 20mg, 101
ID1, AA11, Item1, 20mg, 102
ID1, BB11, Item3, 500mg, 100
ID1, BB11, Item3, 500mg, 99
ID1, BB11, Item4, 10mg, 103
ID1, BB11, Item4, 10mg, 104
ID1, BB11, Item2, 30mg, 98
ID1, BB11, Item2, 30mg, 105
order by Item;
DROP table table1;
=Concat(DISTINCT Strength,',', sortkey)
I'm not sure if this is the optimal solution but it worked for me.
View solution in original post
can you please suggest any ideas? I appreciate your time.
Why not using always numeric values - adjusted within the script, maybe like:
dual(strength, keepchar(strength, '0123456789')) as strength
and then using them directly as sorting within the concat() without the aggr() and also the same sorting for all the different concat() and maybe a distinct statement to exclude duplicates.