Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
schmidtkm37
Contributor III
Contributor III

limit dimension to one match

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.

2 Replies
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
Partner
Partner

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