topic Re: a dual field in QlikView

a dual field

Anonymous — Thu, 09 Jul 2015 20:43:19 GMT

Hi,

The field Month is dual in my application - it is both numeric and string.

My task is to get a string value of the minimal Month selected by the user.

The formula min(Month) results in a numeriс value. How can I set it to give a string result?

Thank you in advacne,

Larisa

Re: a dual field

Qrishna — Thu, 09 Jul 2015 20:47:27 GMT

monthname (min(Month)) or month(min(Month))

Re: a dual field

petter — Thu, 09 Jul 2015 20:50:34 GMT

Month( MakeDate(2015, Min(Month) ,1 ) )

Re: a dual field

swuehl — Thu, 09 Jul 2015 21:02:11 GMT

If you have an arbitrary text value and you don't want to re-apply a QV Date function:

=FirstSortedValue( Month, Month)

Re: a dual field

Anonymous — Thu, 09 Jul 2015 21:17:38 GMT

Actually I have an arbitarary text. Month name is in Russian to it's a plane text.

But the function FirstSortedValue( Month, Month) doesn't work in my case. The result is '-'.

I'm quite sure that the field is dual.

Re: a dual field

antoniotiman — Thu, 09 Jul 2015 21:20:24 GMT

Try

FirstSortedValue(DISTINCT Month,Month)

Re: a dual field

Anonymous — Thu, 09 Jul 2015 21:26:04 GMT

It works! Thank you!

How can I get the maximal value?

LastSortedValue doesn't work.

Re: a dual field

antoniotiman — Thu, 09 Jul 2015 21:28:38 GMT

FirstSortedValue(DISTINCT Month,-Month)

Re: a dual field

swuehl — Thu, 09 Jul 2015 21:29:31 GMT

I believe it should return the correct value, assuming Month being a dual value. Please double check.

Create a straight table chart with dimension Month, and an expression

=num(Month)

Please post the screenshot of that chart.

Re: a dual field

Anonymous — Thu, 09 Jul 2015 21:39:37 GMT

Hi swuel,

I've experimented.

It works this way:

FirstSortedValue(distinct Месяц, Месяц) & ' – ' &FirstSortedValue(distinct Месяц, Месяц, Count(distinct Месяц))

FirstSortedValue(distinct Месяц, Месяц) - gets the minimal month

FirstSortedValue(distinct Месяц, Месяц, Count(distinct Месяц)) - gets the maximal month

You and Antonio helped. Thank you!

Re: a dual field

Anonymous — Thu, 09 Jul 2015 21:41:00 GMT

Thanks!

Re: a dual field

MarcoWedel — Thu, 09 Jul 2015 21:43:26 GMT

another one:

Month(Date#(Min(Month),'M'))

regards

Marco

Re: a dual field

swuehl — Thu, 09 Jul 2015 21:49:05 GMT

Hi Larisa,

very,very strange that you need the DISTINCT qualifier here.

Just for interest, could you post a sample QVW with just that field?

Re: a dual field

antoniotiman — Thu, 09 Jul 2015 22:09:34 GMT

Hi swuehl,

from help guide (i'm sorry, original italian) FirstSortedValue()

Se più valori di espressione condividono lo stesso sort-order minimo, la funzione restituirà NULL.

So, You need DISTINCT clause.

Re: a dual field

swuehl — Thu, 09 Jul 2015 22:18:28 GMT

Hi Antonio,

I think in english it's:

If more than one value of expression share the same lowest sort-order, the function will return null.

I haven't considered that this will be enforced even if the 'value of expression' is essentially the same. But you are right.

And I thought the results were strange based on my testing with distinct calendar fields, so it was not the correct test setting to be sure.

Re: a dual field

antoniotiman — Thu, 09 Jul 2015 22:45:22 GMT

Yes, this is misleading behavior.

Concat(Field,Separator,Sortvalue) has different behavior.

E.g. List of Products sorted by sales

Concat(DISTINCT Product,',',Aggr(NODISTINCT Sum(Sales),Product))

Regards,

Antonio