Putting Fields together

[Chris1662]

Hello Qlik Community, I am currently stuck at a problem and i dont know how to solve it.

To demonstrate my problem I will give you an example date set:

Order Number	Product
101	Table
101	Chair
101	TV

In this example I have one order with the same Order Number but it contains three different products. Since its one order i want it to be portrayed only once with the different products in one cell. Like this:

Order Number	Product
101	Table, Chair, TV

I know that I can make this transformation in the table it self with the command:  =concat(distinct Product,', '). The Problem is that this will make "Product" a calculated field which wont allow me to filter for products anymore. Because of loosing the ability to filter for products when using this command in the table editor i want to make this change in the data editor so i can still filter for products but have them in one cell if they contain to the same order.

I hope my problem got clear by this short explanation. Greetings Chris

[Or]

You could keep the original field and also create a concatenated field, though I'm not sure if that's exactly what you're after

Load [Order Number], Product as [Product Filter]

From YourTable;

Load [Order Number], concat(distinct Product,', ') as [Product Display], sum(Value) as Value

From YourTable

Group by [Order Number];

You could then filter by the filter product and use the display product in your table. Note that in this case, if you filter on Chair, you will still get the correct value for the entire order 101, so if your intent is to only get the value for the line containing Chair, you will need to play the Value in the first table rather than the second.

Also note that you can concatenate the values within your actual front-end table by using Concat() in a measure or a calculated dimension (which would require you use aggr() ) in which case you will not need to do anything on the back end.