Using Concatenate in Calculated Dimension

[bhaskarsharma03]

Hi,

I am trying to create a table where I want to aggregate the Value by Deal ID (please see the attached qvw).

One Deal ID can have multiple values based on other dimensions like Deal Type, Region or Technology. Or in other words, the deal value is broken up between some other organizational dimensions.

So, when aggregating the Value by Deal ID, I have taken all other dimensions as expressions and concatenated them so that I can see which all Deal Types/Technologies are involved in a particular deal. However, the problem with this method is, I cannot make a selection in the table – eg: I cannot click on SAP and see all deals belonging to SAP technology (Yellow table in the attached file).

Therefore, I am trying to find a way to do the concatenation on rest of the fields at dimension level, so that the intuitive selection within the table is still possible for the users.

Any idea how to achieve this?

Regards,

Bhaskar

[aveeeeeee7en]

I am not clear with your requirement.

Use Subfield(Technology, ', ')  instead of Concat(Region,', ')

Also, see the Attachment.

Is this what you want.

[Gysbert_Wassenaar · Accepted solution]

What you want isn't possible afaik. But there is an alternative. You can enable the option Searchable for the expression on the Presentation tab. You'll get a magnifying glass icon in the header of the expression. When you click it you can enter a search string. In the below image I've searched for SAP in the Technology expression column:

[bhaskarsharma03]

I understand that this might be a good workaround; but users might get confused as in some tables they can directly select a value and get the table filtered, and in others this doesn't work good.

But if there's no better solution to this, we may have to live with this solution - with a disclaimer that the normal select & filter won't work in this table.

Thanks for your response.

[sujeetsingh]

Gylbert ,

It is very new one man .

[bhaskarsharma03]

The view I am trying to build (Concatenated Dimension) can be done in script. But that will mean that the front end will not be dynamic enough to respond to the selections made on the screen/or the data reduction applied by the section access.

Eg: if someone selects AM as Deal Type (or someone has section access restricted to only AM deals), he/she will still see AM, Consultancy as Concatenated Deal Type for the Deal ID 123.

It would have been really nice to be able to concatenate values of dimensions on the fly and yet ensure that QlikView’s Point & Select functionality working seamlessly in a Straight table.