3 Replies Latest reply: Jun 7, 2012 4:11 PM by B Aydin RSS

    selecting most common text value in an expression

    dcroft

      Hi,

      I have a data model which looks like

      FieldA FieldB

      ABC Text1

      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
          Piet_Orye

          <body><p>One option via pivot table:</p> <p>Dimensions:</p> <p>   FieldB</p> <p>   FieldA</p> <p>Expressions:  </p> <p>   Count(FieldB)   // just for checking</p> <p>   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</p> <p>   if(&#39;&#39; &amp; Rank(Count(FieldB),1,2)=&#39;1&#39;,FieldB)  // actual result: gives Null for all fields except the correct one; you need the &#39; &#39; so that QV does not use the numeric value which gives to times 1 ( dual() value )</p> <p><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"><b>ABC</b></td> <td width="42" class="xl64">DEF</td> <td width="42" class="xl64">DEF</td> <td width="42" class="xl64"><b>DEF</b></td> <td width="42" class="xl64">GHI</td> <td width="42" class="xl64">GHI</td> <td width="42" class="xl64"><b>GHI</b></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"><b>Text1</b></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"><b>Text1</b></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"><b>Text3</b></td> <td align="right" class="xl66">0</td> <td align="right" class="xl64">3</td> <td class="xl64">-</td> </tr> </p> <p>Greetings,</p> <p>Piet</p> <p> </p></body>

          • selecting most common text value in an expression
            Matías Baldoma

            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
              B Aydin

              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/