topic Re: A problem with max of a field function in QlikView

A problem with max of a field function

Fri, 20 Mar 2015 09:43:58 GMT

Hi, guys!

Please help me find a solution. I have a problem here.\

This is my table:

Employee	DataAccount	DateAccount
Pavel Kovalski	1,00	Nov 2014
Pavel Kovalski	1,00	Dec 2014
Pavel Kovalski	1,00	Jan 2015
Pavel Kovalski	1,00	Feb 2015
Kirill Abramov	0,00	Mar 2014
Kirill Abramov	0,00	Oct 2014
Kirill Abramov	0,00	Nov 2014
Kirill Abramov	0,05	Jan 2015
Kirill Abramov	0,14	Sep 2014
Kirill Abramov	0,23	Sep 2014
Kirill Abramov	0,64	Sep 2014
Kirill Abramov	0,95	Jan 2015
Kirill Abramov	1,00	Feb 2014

I need to find Max value of DataAccount for each month.

I try like :

Load Employee, DateAccount ,

Max(DataAccount) as Values

Resident Table2

Group By Employee, DateAccount ;

But it doesnt work, it gives me this :" - " for each eriod . I also tried like Num#(Max(DataAccount)) or Num(Max(DataAccount)), but again no result.

Re: A problem with max of a field function

jyothish8807 — Fri, 20 Mar 2015 09:47:37 GMT

Hi Diana,

Try this:

aggr(max(DataAccount),DateAccount) in your straight chart.

Regards

Re: A problem with max of a field function

Fri, 20 Mar 2015 09:51:46 GMT

Hi, Jyothish!

I need to find this max values in the load script..

Re: A problem with max of a field function

martinpohl — Fri, 20 Mar 2015 09:54:30 GMT

Can't find an error, for me it works.

Re: A problem with max of a field function

Fri, 20 Mar 2015 09:56:08 GMT

Hi,

You can try like this.

Table1:

Load

Employee,

DateAccount ,

Values

from ................;

Table2:

Load

Employee,

DateAccount ,

Max(DataAccount) as Values

Resident Table1

Group By Employee,DateAccount ;

Drop table Table1;

Re: A problem with max of a field function

Fri, 20 Mar 2015 09:59:26 GMT

Hi, Martin!

I guess I have a problem with number format. I can't figure out how to change this format to make this work. As I mentioned above I tried Num#(Max () ) and num (Max ()).

Any suggestions?

Re: A problem with max of a field function

martinpohl — Fri, 20 Mar 2015 10:05:43 GMT

Hi Diana,

maybe your DataAccount can't be interpreted as a number.

For that you can't calculate a max value.

How are the datas shown in a list box? right aligned or left aligned?

if left aligned QlikView thinks that the datas are a string, not a number.

In that case you have to use a syntax like this

max(num(num#(DataAccount,'#,#'))) as Value

num# defines the syntax the datas are in = decimale sting is a comma

than you have to recalulate them as a numeric field with num

and finally you can calculate the max

I can't test it on my system because for the the decimal seperator is comma

Regards

Re: A problem with max of a field function

jyothish8807 — Fri, 20 Mar 2015 10:06:50 GMT

Hi Diana,

Are you using a inline table? If yes then "," between numbers will create an issue.

Or try:

num(num#(Yourfield,'###,###')) as Yourfield

PFA.

Regards

Re: A problem with max of a field function

Fri, 20 Mar 2015 10:10:24 GMT

Hi,

In script remove the comma(,) and put point(.).

Re: A problem with max of a field function

Anonymous — Fri, 20 Mar 2015 10:13:18 GMT

Try this

Load Employee, DateAccount ,

Max(num#(DataAccount,'#,##')) as Values

Resident temp

Group By Employee, DateAccount ;

Re: A problem with max of a field function

Fri, 20 Mar 2015 10:16:32 GMT

Martin,

yes, it is left aligned. I tried to use your formula but it gives me only 0 as numbers

Re: A problem with max of a field function

Fri, 20 Mar 2015 10:24:16 GMT

Also the strange thing is that I made a listbox of DataAccount and it showed me this:

0

-0,01
-0,02
-0,03
-0,04
-0,05
-0,06
0,01
0,02
0,03
0,04
0,05
0,06

For each positive number there is a negative. Why does Qlikview load my values like this? I dont have any negative numbers in my Excel file...

Re: A problem with max of a field function

martinpohl — Fri, 20 Mar 2015 10:25:50 GMT

Hi Diana,

where are your datas from?

have you tried num#(DataAccount,'#,##') ?

As I can see all datas are between 0,05 and 1, so maybe they are percentage values.

If I can't script the script edit to get calculateable values use a trick:

change the values to percentage

0,05 = 5

0,65 = 65

1,00 = 100

so that there are no commas.

load the datas and then recalculate them by devide them by 100

load DataAccount/100 as DataAccount

For this value you can calculate the max.

Regards

Re: A problem with max of a field function

Fri, 20 Mar 2015 10:43:08 GMT

Martin,

It worked perfectly well! Great. Thank You!