limit dimension to one match

[schmidtkm37]

Hello,

I have a situation where I have item numbers and descriptions but some item numbers have 2 descriptions that are spelled slightly differently. This creates problems where my table shows incorrect sales amounts because the sales are split between the 2 different descriptions. How could I create a straight table where I make sure that each item only has 1 description (maybe take the shortest description or something)? I've tried variations of firstsortedvalue(itemdesc, len(itemdesc)) but can't seem to find anything that works.

Example: Item#        Desc                       Sales

                    601             green - shirt        $100

                    032             blue shirt             $300

                    601             green shirt          $45

I want to make sure that in the example above, item #601 has only 1 row and all $145 of sales are on that row. 

Thank you for your help.

[rwunderlich]

You could use the MinString() value.  If you want to add it as Measure, use:

MinString(Desc)

If you want to add it as a Dimension use:

=aggr(MinString(Desc), Item#)

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[SergeyMak]

You can also try Concat(Desc, ','). In this case it will be shown once, but all values will be concatenated.