topic Using the numeric value of a dual type field as field value definition in a set modifier in QlikView

Using the numeric value of a dual type field as field value definition in a set modifier

wdonckers — Wed, 05 Feb 2014 20:50:45 GMT

Hi,

I have loaded a field called 'Rubriek' from Excel. Actually in Excel there's a RubriekID (which is a numeric key) and Rubriek(which is a text).

In the script I loaded it as follows:

LOAD dual(Rubriek,RubriekID) as Rubriek,...

That seems to work fine, I can use this dimension in objects , sort them bu numeric value and choose to display the num or the string.

In an expression where i use set analysis the following gives no results:

sum({$<Rubriek={1320}>}[Sales for the Period_SALES])

If I write the following instead it does work:

sum({$<Rubriek={' SOFTWARE'}>}[Sales for the Period_SALES])

How to overcome this: Working with numericals is supposed to be faster and filtering on text strings is far less accurate than on key numbers. The textstring may even not be unique.

Any tips to spare?

thanks,

Wim Donckers

Re: Using the numeric value of a dual type field as field value definition in a set modifier

swuehl — Wed, 05 Feb 2014 21:02:34 GMT

You can use something like

sum({$<Rubriek={"=1320"}>}[Sales for the Period_SALES])

Re: Using the numeric value of a dual type field as field value definition in a set modifier

rwunderlich — Wed, 05 Feb 2014 21:07:00 GMT

Using just the "=" does not work for me. I believe you have to write it like this:

sum({$<Rubriek={"=Rubriek=1320"}>}[Sales for the Period_SALES])

-Rob

Re: Using the numeric value of a dual type field as field value definition in a set modifier

swuehl — Wed, 05 Feb 2014 21:09:33 GMT

You're right, Rob.

Re: Using the numeric value of a dual type field as field value definition in a set modifier

wdonckers — Wed, 05 Feb 2014 21:58:58 GMT

That works indeed Rob!

Thanks.

What seemed a good idea (having just one field instead of two) does result in rather ugly set statements. Just because set comparison always compares against the text value a dual field.

What I will do is load RubriekID as an additional normal key field and that will make set statements a lot easier e.g. sum({$<RubriekID={1320}>}[Sales for the Period_SALES])

Thanks again.