Filter on a field from an unrellated table

[zagzebski]

I have a field "Salesperson" that I want to filter on.

I also have a concatenate field in another table that has multiple sales people in it.

Table 1

SalesPerson

John

Lisa

Table 2

SalesPerson Concat         Members

Sam|John|Brian                         50

I need to add an expression so that when the user selects the SalesPerson it will search the "SalesPerson Concat" field to see if the  value is contained in that selection.

For example:  if the user selects John then it would retrieve the 50 members, but if the user selects Lisa then it would NOT retrieve any members because Lisa is not contained in the SalesPerson Concat field.

Note: my problem is there is no relationship between these two tables.

Any help is appreciated

Zag

[krishnacbe]

Please try below script.

Table 1

Load Salesperson

from Table1;

Table 2

Load

Subfield of(SalesPerson Concat,'|') as SalesPerson,

Members

from Table2;

Now you will get 3 separate rows and table 2 will be concatenated to Table1.

Hope it helps.

[zagzebski]

Thanks for the reply. The problem is I can't do this because it will make my "members" field wrong. I will get 3 separate rows each with 50 members adding to 150 (which is wrong).

I was hoping to NOT to change script, but to do it in an expression. Is that possible?

[kkkumar82]

Hi,

Can you a sample qvw or data

[sunny_talwar · Accepted solution]

Use a LinkTable to avoid duplication:

Table1:

LOAD * Inline [

SalesPerson

John

Lisa

];

Table2:

LOAD * Inline [

SalesPerson Concat, Members

Sam|John|Brian, 50

];

LinkTable:

LOAD [SalesPerson Concat],

  SubField([SalesPerson Concat], '|') as SalesPerson

Resident Table2;

When Nothing is Selected

When John is Selected

When Lisa is Selected

[marcus_sommer]

I would rather also go the way suggested from krishnacbe and add a counter to them like:

Table 2:

Load

Subfield([SalesPerson Concat],'|') as SalesPerson,

rangesum(substringcount([SalesPerson Concat], '|'), 1) as SalesPersonCounter,

Members

from Table2;

- Marcus

[krishnacbe]

Hi,

Without script change you can't make the two tables interactive.

here another suggestion.. for sum you can use the salesperson concat field and not the Salesperson.

try with below script and make your expression to get the Count(Members) should depend on [Salesperson Concat],

Table 1

Load Salesperson

from Table1;

Table 2

Load

Subfield of(SalesPerson Concat,'|') as SalesPerson,

SalesPerson as [Salesperson Concat],

Members

from Table2;

[kkkumar82]

Hi ,

PF attached

[zagzebski]

Thanks!

[kkkumar82]

Hi ,

I think I have used even the measure in the dimension, you can use Sum(Members) in the expression and remove the second dimension in the chart.