topic maxstring mystery in QlikView

maxstring mystery

Wed, 01 Dec 2010 13:20:05 GMT

Hello... Can anyone explain how this formula is working:

maxstring(if(Dimensions='Product',Dimensions,null())) & ':' &

maxstring(if(Dimensions='Source',Dimensions,null())) & ':' &

maxstring(if(Dimensions='Team',Dimensions,null())) & ':'

Basically Dimensions is an inline table with 3 entries (Product, Source and Team).

I've set Dimensions up as the source for a list box and want to achieve a string showing all items selected in the list box seperated with a colon.

This all works fine... but I just don't understand how it works... I've tried breaking it down into parts but none of this seems to make sense!?!

Many thanks! Mike

maxstring mystery

Wed, 01 Dec 2010 13:36:35 GMT

Hello,

maxstring( if(Dimensions='Product',Dimensions,null() ) )

If() returns Dimensions only when Dimensions ='Product' (then of course it returns 'Product')
maxstring isn't necessary because if() returns only one Value ('Product') or null()
& means concat result above with ';' and the result of the next if()

repeat above for the other if()-statments.

BUT there is an alternative:

='Selections for Dimensions: ' & getfieldselections(Dimensions))

Or the John Witherspoon Version I found in this formu a while ago: (Thanks John!)

='Selections for VDatum:
' & if(max(VDatum)-min(VDatum)+1=getselectedcount(VDatum),min(VDatum) & ' - ' & max(VDatum),getfieldselections(VDatum))

maxstring mystery

Wed, 01 Dec 2010 14:49:08 GMT

Hi Roland, thanks for the reply...

Some things I'm still unsure about...

1 - If I remove the maxstring function the formula just returns " - "

2 - Also if I do a simple formula like: =if(Dimensions = 'Product', 'Product Selected','Product Not Selected')
this evaluates as I'd expect when I select Product from the list box but when I
select Product and another item the if statement evaluates as 'Product Not Selected'

I've attached a sample doc if that helps understand...

Cheers, Mike

maxstring mystery

Wed, 01 Dec 2010 19:42:07 GMT

Hi Mike,

look at my little application and play with it. The most exciting question is the returnvalue of Maxstring() in combination with a nested if() and NO selection:

=maxstring( if(Dimensions='Product', Dimensions, null()) )

in a text object.

Even when QV-help says "These functions can only be used on fields in chart expressions." Is it a bug or a feature?

Regards, Roland

maxstring mystery

Thu, 02 Dec 2010 10:47:32 GMT

Hum... very strange...

Thanks for your extra comments... so now I can understand that when multiple items are selected in the list box, then displaying 'Dimensions' in a text box is impossible due to the multi-valued nature. If only one item is selected then a text box will display the single result.

So if I select all three items in the list box (Product, Source and Team) then this code:

=':' &maxstring( if(Dimensions='Product',Dimensions,null())) & ':'
& maxstring(if(Dimensions='Source',Dimensions,null())) & ':'
& maxstring(if(Dimensions='Team',Dimensions,null())) & ':'

returns " :Product:Source:Team: " but I would have thought it shoud return " :Team:Team:Team: " because the 3 if statments would each return a list of the 3 selected items and 'Team' is the maxstring for each of the 3 if statement results ?!?

maxstring mystery

Thu, 02 Dec 2010 15:37:05 GMT

Hi Mike,

yes, the behaviour the combination maxstring() and a nested if() is strange. I will ask our technical QT-rep. for an explanation.

maxstring mystery

Oleg_Troyansky — Thu, 02 Dec 2010 15:41:36 GMT

Let me help you simplify - instead of multiple MaxString, try function CONCAT() :

CONCAT(Dimensions, ';')

You'll get a list of all "possible" (selected or associated) Dimensions separated by semicolon.

maxstring mystery

Thu, 02 Dec 2010 16:06:11 GMT

Hi Oleg,

thanks for your reply. The main questions is: Why does the following expression in a textbox show 'Product' when nothing is selected .

=maxstring( if(Dimensions='Product', Dimensions, 'null()')

In my mind the if() has to return 'null()'. And maxstring of null() is what? For testing look into my little app above.

maxstring mystery

Thu, 02 Dec 2010 16:19:03 GMT

And I'd like to know why that line would return 'Product' even if 'Team' is also selected... as "Team" would 'greater than' "Product" in a maxstring function...

maxstring mystery

Thu, 02 Dec 2010 18:13:43 GMT

CONCAT(Dimensions, ';')

also shows all items if none are selected...

maxstring mystery

Oleg_Troyansky — Thu, 02 Dec 2010 23:15:12 GMT

Yes, both functions are working with all POSSIBLE values - they don't have to be selected.

If you are after Selected values only, you need to use different functions like GetFieldSelections:

GetFieldSelections( fieldname [, valuesep [, maxvalues=6 ]] )

returns a string containing the current selections in a given field. Valuesep is the delimiter between selected values and defaults to ', '. Maxvalues is the highest number of individual values that will be displayed before 'x of y' notation is used instead.

Examples:

getfieldselections(Country)
getfieldselections (Country, ';' )
getfieldselections (Country, ',' , 10 )