Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
manideep78
Partner
Partner

Concat Sort-Weight not working with string Columns

Hi All,

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

IDCodeItemstrengthREC_Num
ID1AA11Item120mg101
ID1AA11Item120mg102
ID1BB11Item3500mg100
ID1BB11Item3500mg99
ID1BB11Item410mg103
ID1BB11Item410mg104
ID1BB11Item230mg98
ID1BB11Item230mg105

 

Expected Output:

Dimension - ID, Code

Expressions - Item, Strength

IDCodeItemStrength
1AA11Item120mg
1BB11Item2,Item3,Item430mg, 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

 

Labels (2)
1 Solution

Accepted Solutions
manideep78
Partner
Partner
Author

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

 

 

View solution in original post

3 Replies
manideep78
Partner
Partner
Author

Hi All,

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Partner
Partner
Author

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

 

 

View solution in original post