Comparing to fields in Set Analysis

[mario-sarkis]

Hey All,

Here is my question I need to calculate the sum of Amount if the "From ID" is Deferent Then "To ID" Example :

From ID      To ID       Amount

1                  1            $1,000

1                   2           $3,000

1                   5           $4,000

Aggregation  OUT:

From ID            Total Amount

1                           $7,000

Hope you can help,

Thank you.

[fvelascog72]

Hi,

This can help:

Sum(If([From ID] <> [To ID], Amount))

Saludos.

[vinieme12]

Two ways to do this

Option1:

use expression

          = SUM(if( FromID <> ToID , Amount))

Option2:

Create key in script that identifies each row separately/uniquely

LOAD * , Rowno() as RowKEY INLINE [

From ID    ,To ID ,      Amount

1                ,  1    ,        $1,000

1                 ,  2  ,         $3,000

1                  , 5 ,          $4,000

];

Then in chart use Expression

=Sum( {< RowKEY = {"=FromID<>ToID"} >} Amount)

[sebastianlettner]

Hallo,

i would create a flag in the script using the folluwing code

LOAD

     [From ID],

     [To ID],

     Amount,

     If([From ID] <> [To ID], 1, 0) as Flag

If you have this Flag field you can use this expression

Sum(Amount * Flag)

Regards

Sebastian

[andrei_delta]

Hello,

What you are looking for is this: sum({<[To ID]-=P([From ID])>} Amount)

The result:

Hope it hels,

Andrei

[effinty2112]

Hi Andrei,

Try your expression with this data:

LOAD * INLINE [

    From ID,      To ID,      Amount

    1,                  1,            $1000

    1,                  2,          $3000

    2,                  3,          $4000

];

The correct answer should be $7,000 but your expression, sum({<[To ID]-=P([From ID])>} Amount), will return $4,000. The second line should be included in the sum because for this record [From ID] <> [To ID]. Your expression excludes all Amounts that have a [To ID] value that appears as a value for [From ID] for any record.

Kind regards

Andrew

[effinty2112]

Hi Mario,

You have lots of choices, here's one more:

=sum({$<Amount = {"= [From ID] <> [To ID]"}>}Amount)

Regards

Andrew

[vinieme12]

this will fail when the Amount value is similar to another row which actually satisfies the condition

for example try with

LOAD * INLINE [

    From ID,      To ID,      Amount

    1,                  1,            $3000

    1,                  2,          $3000

    2,                  3,          $4000

];

[effinty2112]

Hi Vineeth,

You're right. I really need a unique ID. Sloppy of me to think Amount could be used.

Thanks

Andrew