selecting most common text value in an expression

Thu, 18 Mar 2010 16:53:18 GMT

Hi,

I have a data model which looks like

FieldA FieldB

ABC Text1

ABC Text2

DEF Text3

GHI Text1

GHI Text2

I have 2 questions

1) How do I , in an expression, select the most commonly occuring value in FieldB for FieldA

2) Assuming this can be done, how can I be deterministic in the case of GHI above ie. always select the same value for FieldB

Thanks.

selecting most common text value in an expression

Thu, 18 Mar 2010 17:54:33 GMT

<body>One option via pivot table: Dimensions: FieldB FieldA Expressions: Count(FieldB) // just for checking Rank(Count(FieldB),1,2) // just for checking: shows the ranking; for doubles only the first one by sort order has a number, the rest Null if('' & Rank(Count(FieldB),1,2)='1',FieldB) // actual result: gives Null for all fields except the correct one; you need the ' ' so that QV does not use the numeric value which gives to times 1 ( dual() value ) <col width="42"></col> <col width="42"></col> <col width="42"></col> <col width="42"></col> <col width="42"></col> <col width="42"></col> <col width="42"></col> <col width="42"></col> <col width="42"></col> <col width="42"></col> <tr> <td width="42" class="xl63" height="17">FieldA</td> <td width="42" class="xl64">ABC</td> <td width="42" class="xl64">ABC</td> <td width="42" class="xl64">ABC</td> <td width="42" class="xl64">DEF</td> <td width="42" class="xl64">DEF</td> <td width="42" class="xl64">DEF</td> <td width="42" class="xl64">GHI</td> <td width="42" class="xl64">GHI</td> <td width="42" class="xl64">GHI</td> </tr> <tr> <td class="xl65" height="17">FieldB</td> <td class="xl64">C</td> <td class="xl64">N</td> <td class="xl64">V</td> <td class="xl64">C</td> <td class="xl64">N</td> <td class="xl64">V</td> <td class="xl64">C</td> <td class="xl64">N</td> <td class="xl64">V</td> </tr> <tr> <td class="xl64" height="17">Text1</td> <td align="right" class="xl66">2</td> <td align="right" class="xl64">1</td> <td class="xl67">Text1</td> <td align="right" class="xl66">0</td> <td align="right" class="xl64">2</td> <td class="xl64">-</td> <td align="right" class="xl66">1</td> <td align="right" class="xl64">1</td> <td class="xl67">Text1</td> </tr> <tr> <td class="xl64" height="17">Text2</td> <td align="right" class="xl66">1</td> <td align="right" class="xl64">2</td> <td class="xl64">-</td> <td align="right" class="xl66">0</td> <td align="right" class="xl64">2</td> <td class="xl64">-</td> <td align="right" class="xl66">1</td> <td align="right" class="xl64">1</td> <td class="xl64">-</td> </tr> <tr> <td class="xl64" height="17">Text3</td> <td align="right" class="xl66">0</td> <td align="right" class="xl64">3</td> <td class="xl64">-</td> <td align="right" class="xl66">1</td> <td align="right" class="xl64">1</td> <td class="xl67">Text3</td> <td align="right" class="xl66">0</td> <td align="right" class="xl64">3</td> <td class="xl64">-</td> </tr> Greetings, Piet </body>

selecting most common text value in an expression

mbaldoma — Thu, 07 Jun 2012 14:36:53 GMT

firstsortedvalue(FieldB,-aggr(max(aggr(count(FieldB),FieldB,FieldA)),FieldA))

But, I can not make it deterministic, always depends on the load order.

Re: selecting most common text value in an expression

Thu, 07 Jun 2012 18:27:56 GMT

Hi,

For item 1, you can try the following expression.

if(isnull(mode(FieldB))=-1,MaxString(FieldB),mode(FieldB))

I could not understand exactly what you ask in item 2.

Best regards,

http://quickdevtips.blogspot.com/

topic selecting most common text value in an expression in QlikView

selecting most common text value in an expression

selecting most common text value in an expression

selecting most common text value in an expression

Re: selecting most common text value in an expression