topic Set Analysis and concat in QlikView

Set Analysis and concat

Wed, 08 Sep 2010 16:23:01 GMT

Hi all,

I have a chart expression:

sum({1<[Ordering Depot]={'$(=concat(distinct Depot))'}>} [Number Orders])

but this always (incorrectly) returns 0

I am using the dollar-sign expansion so that it returns the results of the concat in single quotes as 'Depot' is a text field which may contain spaces.

If I create a chart and put any of these expressions into it:

=concat(distinct Depot)
$(=Year(Today()))
$(=concat(distinct Year(Today())))

they return the expected values but if I try

$(=concat(distinct Depot))

it returns nothing.

I am obviously missing something, but what?

Any help appreciated.

Gordon

Set Analysis and concat

Miguel_Angel_Baeyens — Wed, 08 Sep 2010 16:46:20 GMT

Hello Gordon,

My guess is that since concat has no delimitator, is concatenating one field after another, and since they are text fields, they need a precedeing and trailing comma taht concat does not add, so

$(=chr(39) & Concat(distinct Año, chr(39) & ',' & chr(39)) & chr(39))

will do the trick.

Hope that helps!

Set Analysis and concat

johnw — Wed, 08 Sep 2010 17:25:04 GMT

I think you should be able to use another field as an element set:

sum({1<[Ordering Depot]=Depot>} [Number Orders])

Set Analysis and concat

Wed, 08 Sep 2010 17:29:55 GMT

Hi Miguel,

Thanks very much for your reply.

I understand what you are saying but I couldnt get it to work:

sum({1<[Ordering Depot]={$(=chr(39) & Concat(distinct Depot, chr(39) & ',' & chr(39)) & chr(39))}>} [Number Orders])

I actually found 'only' works and here is my expression:

sum({1<[Ordering Depot]={'$(=only(Depot))'}>} [Number Orders])

I am still intrigued why concat doesnt though.

Regards,

Gordon

Set Analysis and concat

Wed, 08 Sep 2010 17:40:53 GMT

Hi John,

Thanks for your input.

I agree it should be as simple as that, but I found that it also returned 0 and that started me off on the road to concat. Again I dont know why it doesnt work.

Regards,

Gordon

Set Analysis and concat

Miguel_Angel_Baeyens — Wed, 08 Sep 2010 17:54:56 GMT

Gordon,

I'm probably missing something also. Anyway, you may try setting a variable with the whole analysis, say vAnalysis

'{' & chr(39) & Concat(distinct Depot, chr(39) & ',' & chr(39)) & chr(39) & '}'

and then in your expression

Sum({1< [Ordering Depot] = $(vAnalysis) >} [Number Orders])

Might work. I also agree that John's expression might work and is cleaner by far, though.

Regards.

Set Analysis and concat

johnw — Wed, 08 Sep 2010 18:00:15 GMT


gordon.savage wrote:I agree it should be as simple as that, but I found that it also returned 0 and that started me off on the road to concat. Again I dont know why it doesnt work.

Weird. Maybe this?

sum({1<[Ordering Depot]=P(Depot)>} [Number Orders])

But it sounds like you have a working solution already. I agree that the concat() needs the extra characters, but at that point I'd have expected it to work. I've done it before, even if only in examples, and I'm not seeing anything wrong with your syntax on brief glance.

Set Analysis and concat

Thu, 09 Sep 2010 11:06:37 GMT

Hi Miguel/John,

Miguel - again nice thinking but the expression editor sees it as an error. I also tried changing the variable and removed the curly brackets and put them around the variable in the expression. The editor was happy this time but again nothing was returned.

John - the elemental function P() works great and one that I wasnt aware of.

Thanks for your inputs.

Regards,

Gordon

Re: Set Analysis and concat

bruno_m_santos — Fri, 23 Aug 2013 13:27:50 GMT

Hi Guys,

I've a related problem.

Set Analysis restrict months depending on other expression

Could you give me some help, with your expertise?

Thanks

Bruno