Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Dimensions and other fields

Hi everybody:

This is the problem:

I have a table that comes in this way:

universal_id    name             amount

123456          John Smith          100

123456          John Smith          350

123456          John Smith          220

789123          Tom Wilson          110

789123          Wilson Tom          400

789123          Tom Wilson          150

789123          Tom Wilson          210

Now, in a straight table, I set 'universal_id' as dimension, and this is the result

universal_id    name             sum(amount)     count(universal_id)

123456          John Smith          670               3

789123          --                  870               4

"Name" comes from different sources, and because of that, when they are different, it can't be "consolidated"

I would like the result would be:

universal_id    name             sum(amount)     count(universal_id)

123456          John Smith          670               3

789123          Tom Wilson          870               4

I tried several ways, but no one went ok

Any suggestions?

Thanks

6 Replies
Highlighted

Re: Dimensions and other fields

You could make name an expression instead of a dimension and use FirstSortedValue() to only get one of the values:

=FirstSortedValue(name, name)

Highlighted
Master III
Master III

Re: Dimensions and other fields

May be

MaxString(name) or MinString(name) instead of name (expression)

Highlighted

Re: Dimensions and other fields

May be like attached

Capture.PNG

Highlighted
Contributor
Contributor

Re: Dimensions and other fields

Hi Nicole:

I have tested your suggestion; did not work in both forms (added another dimension and/or using FirstSortedValue as you mentioned).

When using FirstSortedValue as expression, result is NULL for all

Highlighted
Contributor
Contributor

Re: Dimensions and other fields

Yes! this worked!

Using MaxString(name) instead of name, solved the problem!.

Now, always show a name

Thank you, and everybody in the forum!

Highlighted
Contributor
Contributor

Re: Dimensions and other fields

The way you show the ids (in any order, or unsorted) is the natural way in which they come.

I used the example of sorted ids, just to be clear to expose the problem to the forum.

Anyway, thanks to everybody in the forum by your fast answers