could be a concatenate or join

[#1]

Hi,

shouldn't be too difficult but I tried with no result.

If yoou take a look at the example I'm attaching,

what I want is a single chart summing values from Person1 column and Person2 column.

What I need to calculate is how many action a person performed, and it's not relevant if that person is in Person1 column or in Person2 column.

For example John has 2 in the firs chart and 1 in the second chart, I need only one chart where John is going to have a 3.

I think I should concatenate Person1 column and Person2 column in a new table. So I won't merge any row, I will obtain a new table containing the sum of the rows from the two input tables and only one "Person column". It's important to have a new table as I'm using the original table for other purposes.

Thanks a lot in advance,

Lorenzo

[swuehl]

You can concatenate the two fields in a new table or use a crosstable load to transform the structure:

CROSSTABLE (Person, Name,1) LOAD * INLINE [

    Action, Person1, Person2,

    Action1, John, Robert,

    Action2, Robert, John,

    Action3, Bruce, Robert,

    Action4, Kerry, James,

    Action5, John, Kerry,

    Action6, Bruce, Robert

    Action7, Laura, Andrew

];

will create a table with three fields Action, Person, Name.

Here you still can filter the Names by using something like

=count({<Person = {Person1}>} Name)

resp.

=count({<Person = {Person2}>} Name)

Hope this helps,

Stefan

[#3 · Accepted solution]

Hi yo can use a Concatenate but you must be divide your inline in two tables and after that use a concatenate. After that you can Use a resident using load Distinct or simply use count(Distinct in your chart.

Remember that Join is used when you need aditional fields.

The script sentence would be:

Movies_Tmp:

LOAD * INLINE [

    Action, Person

    Action1, John

    Action2, Robert

    Action3, Bruce

    Action4, Kerry

    Action5, John

    Action6, Bruce

    Action7, Laura

];

Concatenate

LOAD * INLINE [

    Action, Person,

    Action1,  Robert,

    Action2, John,

    Action3,  Robert,

    Action4, James,

    Action5,  Kerry,

    Action6,  Robert

    Action7, Andrew

];

/* If you don´t want to use disctinct in your chart uncomment the next sentences */

//NoConcatenate

//Movies:

//LOAD

//          Action,

//          Person

//Resident Movies_Tmp;

//DROP Table Movies_Tmp;