topic Re: concat expression in QlikView

concat expression

Mon, 30 Jun 2014 10:46:08 GMT

Hi,

In a table chart I'm trying to do a simple expression using a value from the same row but different column, something like this:

Table:

Sales Rep
Expression =sum({<SalesRep_IslandTable={'"'&concat(DISTINCT [Sales_Rep])&'"'}>}Stores_Count)

=sum({<Level1_PC={$(='"'&concat(DISTINCT [Reps Level1])&'"')}>}MonthID_HMRRegionID_PharmacyCount_PC)

the formula

='"'&concat(DISTINCT [Reps Level1])&'"'

works fine in a different column, but when I introduce it the formula I want, it doesn't work...

What am I doing wrong?

Thanks in advance,

Francisco.

Re: concat expression

Gysbert_Wassenaar — Mon, 30 Jun 2014 10:52:04 GMT

Set analysis on other fields of the same record often doesn't work like you expect. See this example for an explanation: set_analysis_intra-record.qvw

Re: concat expression

DavidFoster1 — Mon, 30 Jun 2014 11:13:39 GMT

This would be better titled as a SET ANALYSIS query more than a concatenate.

Try:

=sum({<SalesRep_IslandTable=P({$}[Sales_Rep])>}Stores_Count)

This should restrict the store_count to only those related to the currently possible values of Sales Rep

Re: concat expression

Tue, 01 Jul 2014 09:28:55 GMT

I understood I could not do it like that, because of the logical calculation of the table chart...

I worked around this problem with triggers to the island table and a different set analysis...

Thanks anyhow,

Francisco

Re: concat expression

Tue, 01 Jul 2014 09:32:18 GMT

Thanks David but I needed 1 to 1 the Sales Reps (model table <-> island table).

I found a way to solve my problem...

Thanks and regards,

Francisco.