topic How do I aggregate data from Dimension 2 for Dimension 1? in QlikView

How do I aggregate data from Dimension 2 for Dimension 1?

Anonymous — Sun, 23 Oct 2016 13:39:05 GMT

Greetings,

I really did not know what to call this, so I will give you my scenario and hope that someone has accomplished this already

I have a dimension that has 500 values. In my example it is model of cars. Each car can have multiple colors. Dimension 2 is the color. My goal is to create a straight table where I can aggregate the possible colors for each model into one cell. It is possible for there to be 10 red mustangs, so I would only want red listed once in the Aggregation expression. Optimally the values in the expression are separate by commas, but any other delimiter is fine. There is a unique field that has a ID number for each car, but I will not use that in the chart, so there are not really duplicate records in the table of data.

Unfortunately, I can not share my actual data, but if you can solve this problem, I can apply it to my data model.

Re: How do I aggregate data from Dimension 2 for Dimension 1?

swuehl — Sun, 23 Oct 2016 13:46:16 GMT

Try creating a straight table chart with dimension [Car Model] and expression

=Concat(DISTINCT [Color], ', ')

Re: How do I aggregate data from Dimension 2 for Dimension 1?

Anonymous — Sun, 23 Oct 2016 14:26:23 GMT

Thanks for your quick response on a Sunday!

That works great except that it puts a comma after the last value

Red, Blue, Green,

The simple solution is to change the delimiter to a space, but is there a way to tell it to only put the delimiter between the values and not after the last possible one?

Red, Green, Blue

Re: How do I aggregate data from Dimension 2 for Dimension 1?

swuehl — Sun, 23 Oct 2016 14:38:42 GMT

It shouldn't work like this, there should not be an extra delimiter after the last value.

Could you post your exact expression you are using?

Re: How do I aggregate data from Dimension 2 for Dimension 1?

Anonymous — Sun, 23 Oct 2016 14:49:23 GMT

Stefan,

You are correct, I had an '&' between the dimension and the delimiter, so it was adding the delimiter each time. It was left over from my previous efforts and I had not removed it. When I use your exact syntax, I do get the desired results. I went ahead and added a space after the comma in the delimiter ', ' so that I could read the results easier. I do appreciate your assistance!