topic Concat Question in QlikView

Concat Question

Mon, 25 Oct 2010 14:39:20 GMT

Hi everybody,

I'm working on this Concat-formula and get again no result.

LOAD * INLINE [

Years
...,
2007,
2008,
2009,
2010

]

=max( concat( {$} distinct Years, ',') )

The result should be 2010.
you know what it is

Concat Question

Mon, 25 Oct 2010 14:41:52 GMT

Concat creates a comma separated list, which the Max function can't handle. Try:

Rangemax(Concat({1} distinct Years, ','))

Also, why can't you use:

Max(Years)

Concat Question

Mon, 25 Oct 2010 14:42:48 GMT

Why do you want to use concat and max ad the same time?

Concat Question

Mon, 25 Oct 2010 14:44:18 GMT

just use: =max(Year). Than you get the same result

AW:Concat Question

Mon, 25 Oct 2010 14:48:22 GMT

Hi,

thanks for the Answer,

I want to compare selected years.

The formula in the chart is:

=Sum(
{$<Years={
$(=(max(concat({$} distinct Years,','))))
}
} sales

...

AW:Concat Question

Mon, 25 Oct 2010 14:58:57 GMT

Did you try Rangemax()? The Range functions take a comma separated list and pick out the biggest value (or smallest, etc depending on which one you're using). If you can't simply use Max(Years), then Rangemax(Concat({1} distinct Years, ',')) will do what you're trying to do.

AW:Concat Question

jonathandienst — Mon, 25 Oct 2010 15:15:40 GMT

I think you can get what you are trying with

=Sum({$<Years={ $(=(max(Years)))}>} sales)

This will sum the sales amounts for records where the year is the latest in the file (2010 in your example)\

Hope that helps

Jonathan

Edit: Formula type corrected

AW:Re: AW:Concat Question

Mon, 25 Oct 2010 15:34:34 GMT

=Sum({$} sales)

AW:Re: AW:Concat Question

jonathandienst — Mon, 25 Oct 2010 15:38:14 GMT

Not sure what you mean by that. Is it a question or an answer?

Jonathan

AW:Concat Question

Mon, 25 Oct 2010 15:41:40 GMT

ignore the last post

your formula
=Sum({$<Years={ $(=(max(Years)))}>} sales)
is correct.

But if I want to compare the years 2001,2003,2009, I need the Concat function

Here is the solution:
Max({$<Years={$(=(concat({$} DISTINCT Years,',')))}>}DISTINCT Years)

The Complete solution I'll post when everything is ready

AW:Concat Question

jonathandienst — Mon, 25 Oct 2010 15:48:46 GMT

I stand open to correction on this, but as far as I can see

Max({$<Years={$(=(concat({$} DISTINCT Years,',')))}>}DISTINCT Years)

will return the same value as Max(Years), only more complex and less efficient. Am I missing something?

Jonathan

AW:Re: AW:Concat Question

Mon, 25 Oct 2010 15:54:36 GMT

Yes, you're right.
Years: 2001,2003,2006
If I need the Max value, then I can Max (Years) take. But if I need the second point, I think, i must work with Concat.
For example i need the Year 2003

Or is there another, better solution?......

AW:Re: AW:Concat Question

jonathandienst — Mon, 25 Oct 2010 15:58:41 GMT

OK, but how are you selecting the year that you need?

AW:Re: AW:Concat Question

Mon, 25 Oct 2010 16:03:18 GMT

Many roads lead to rom and i chose the difficult road ahead

i select the years on the ListBox

Max(Years,2)
Max(Years,3)
...

I had to use this formula.
=Sum({$<Years={$(=max(Years,2))}>} sales)

Thanks for your help

Re: AW:Concat Question

Thu, 17 Jan 2013 14:33:21 GMT

Hi,

The formula does not work. The concat is OK but when you set it as the argument of the rangemax, the result is null...