topic Re: Dimensions and other fields in QlikView

Dimensions and other fields

rdomenoqv — Mon, 06 Nov 2017 15:07:40 GMT

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

Re: Dimensions and other fields

Nicole-Smith — Mon, 06 Nov 2017 16:54:50 GMT

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

=FirstSortedValue(name, name)

Re: Dimensions and other fields

antoniotiman — Mon, 06 Nov 2017 17:03:34 GMT

May be

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

Re: Dimensions and other fields

sunny_talwar — Mon, 06 Nov 2017 17:03:55 GMT

May be like attached

Re: Dimensions and other fields

rdomenoqv — Mon, 06 Nov 2017 21:01:38 GMT

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

Re: Dimensions and other fields

rdomenoqv — Mon, 06 Nov 2017 21:03:45 GMT

Yes! this worked!

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

Now, always show a name

Thank you, and everybody in the forum!

Re: Dimensions and other fields

rdomenoqv — Mon, 06 Nov 2017 21:08:37 GMT

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