Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
<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('' & 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 )</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>
firstsortedvalue(FieldB,-aggr(max(aggr(count(FieldB),FieldB,FieldA)),FieldA))
But, I can not make it deterministic, always depends on the load order.
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/
