topic Re: Concat Sort-Weight not working with string Columns in QlikView

Concat Sort-Weight not working with string Columns

manideep78 — Wed, 21 Jul 2021 13:28:12 GMT

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

Re: Concat Sort-Weight not working with string Columns

manideep78 — Wed, 21 Jul 2021 15:05:28 GMT

Hi All,

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

Re: Concat Sort-Weight not working with string Columns

marcus_sommer — Thu, 22 Jul 2021 12:16:07 GMT

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

Re: Concat Sort-Weight not working with string Columns

manideep78 — Thu, 22 Jul 2021 15:55:54 GMT

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