Concat Sort-Weight not working with string Columns

[manideep78]

Hi All,

I have a trouble in using sort-weight in QlikView Concat function in pivot table expression.
Input:

ID	Code	Item	strength	REC_Num
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

Expected Output:

Dimension - ID, Code

Expressions - Item, Strength

ID	Code	Item	Strength
1	AA11	Item1	20mg
1	BB11	Item2,Item3,Item4	30mg, 500mg, 10mg

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.

TIA

[manideep78]

Hi All,

can you please suggest any ideas? I appreciate your time.

[marcus_sommer]

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.

- Marcus

[manideep78 · Accepted solution]

Hi @marcus_sommer 

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.

table1:
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
];


table2:

LOAD *,
AutoNumberHash128(Item,Strength)as sortkey 
Resident table1
order by Item;


DROP table  table1;

Concat Expression:

=Concat(DISTINCT Strength,',', sortkey)

I'm not sure if this is  the optimal solution but it worked for me.

Thanks