Fields in Combination

[#1]

Hi everyone,

First, I just want to say thank you - I've been posting many questions recently, and I appreciate all your responses.

I have an attached an app for you to look at - here is my problem:

I have Orders that are assigned to a pair of Company1 and Company2. My client is interested in searching for a company and seeing all the Orders and Amounts, whether that company is in Company1 or Company2. So for example in this app, if the client wanted to look at company 'A', he would get Orders u,v,x,y.

In the app, you'll see that I found a way to do this for the table, by using P() in sum(Amounts). However, the problem with this method is that if I select 'A' in Company1 to start, although the table is correct, the Orders list box filters out Order x, which has A in Company2. I want x to be available for further analysis.

How do I create a list box that will allow me to select a company if it is either in Company1 or Company2, and therefore bring up all Orders and sum all Amounts?

Perhaps I need to create a new column with a list of all the companies I want to search? I do not have to use the P() expression - whatever works. Thank you again in advance for your time.

[MK_QSL]

is this right ?

'A', he would get Orders u,v,x,y.

[#3]

Sorry, I updated the app before I attached -

He/she would get t, u, v, x, y.

[MK_QSL]

Use below script

Data:

LOAD * INLINE [

    Order, Company1, Company2, Amounts

    t, B, A, 7

    u, A, G, 15

    v, A, A, 10

    v, A, B, 20

    w, B, C, 2

    w, B, D, 4

    x, C, A, 10

    y, A, A, 5

    z, D, F, 10

];

Final:

Load Order, Company1 as Company, 1 as Flag, Amounts Resident Data;

Load Order, Company2 as Company, 2 as Flag, Amounts Resident Data;

Drop Table Data;

Now use below expression

=Concat({<Company = {'$(=GetFieldSelections(Company))'}>}Distinct Order,' ,')

[maxgro · Accepted solution]

Data:

LOAD rowno() as id, * INLINE [

    Order, Company1, Company2, Amounts

    t, B, A, 7

    u, A, G, 15

    v, A, A, 10

    v, A, B, 20

    w, B, C, 2

    w, B, D, 4

    x, C, A, 10

    y, A, A, 5

    z, D, F, 10

];

Data2:

load id, Company1 as Company, 1 as Type Resident Data;

load id, Company2 as Company, 2 as Type Resident Data;

[#6]

Boom! Thanks guys. Have to give Massimo the "Correct" because I get to keep Company1 and Company2 with his method...