Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2010-03-18 12:53 PM

selecting most common text value in an expression

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.

9,515 Views
0 Likes
3 Replies
Not applicable
‎2010-03-18 01:54 PM
Author

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

496 Views
0 Likes
mbaldoma
Partner - Contributor
Partner - Contributor
‎2012-06-07 10:36 AM

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

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

496 Views
0 Likes
Not applicable
‎2012-06-07 02:27 PM
Author

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/

496 Views
0 Likes