topic troubles with set analysis and aggr in QlikView

troubles with set analysis and aggr

Tue, 30 Apr 2013 14:26:29 GMT

I have a qlikview document containing data about some customers. There is CustomerID field and a CustomerName field, but CustomerName is not always present in the data.

I want to concatenate all the CustomerID that have an associated CustomerName in a TextObject, without regard for the current selection state.

I first tried the following expression:

=concat(aggr(distinct CustomerName, CustomerID))

It works but only returns value associated with the selected/possible values of CustomerID. So I added {1} to my expression to evaluate it on the complete data set:

=concat(aggr(distinct {1} CustomerName, CustomerID))

then:

=concat({1}aggr(distinct CustomerName, CustomerID))

and even:

=concat({1}aggr(distinct {1}CustomerName, CustomerID))

But none of the above worked as I want. How can I achieve what I want?

Re: troubles with set analysis and aggr

Gysbert_Wassenaar — Tue, 30 Apr 2013 14:32:10 GMT

Use a straight table instead with CustomerID as dimension and concat({1} distinct CustomerName) as expression

Re: troubles with set analysis and aggr

Tue, 30 Apr 2013 14:36:35 GMT

Unfortunately I have to use a single expression and not a chart object with dimensions and expressions. Is it possible?

Re: troubles with set analysis and aggr

Gysbert_Wassenaar — Tue, 30 Apr 2013 14:53:19 GMT

Maybe concat({1} distinct aggr(concat({1} distinct CustomerName),CustomerID))