Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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