Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi All,
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.
- Marcus
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