topic Re: Help with an expression in QlikView

Help with an expression

Tue, 24 Apr 2012 04:22:18 GMT

Hello.

I need to group clients by product category. For that i have a straight table with ID in first column, concatenated categories in the second, and in the third column i need the name of the category which occurs most in the second column.

How do i find the rank of eatch CatName in column 2 (by row, not column) and display the one with highest rank in column 3?

Client	concat(CatName,',')	Group
1	Cat1,Cat1,Cat2,Cat3	Cat1
2	Cat1,Cat2,Cat2	Cat2
3	Cat1,Cat2,Cat3
4	Cat1,Cat1,Cat1,Cat2	Cat1
5	Cat1,Cat2,Cat3;cat3	Cat3

Thanks in advance.

Re: Help with an expression

Tue, 24 Apr 2012 05:00:43 GMT

Hi,

For that we have a function which returns the most occurring value. The function name is mode().

in your scenario you can use it as mode(CatName) in your third column.

Ashutosh

Help with an expression

Tue, 24 Apr 2012 05:49:21 GMT

Thank you. Your reply was quick and correct I had no idea this sort of function existed.