topic Re: Comparing to fields in Set Analysis in QlikView

Comparing to fields in Set Analysis

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Comparing to fields in Set Analysis

fvelascog72 — Fri, 15 Sep 2017 08:00:03 GMT

Hi,

This can help:

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

Saludos.

Re: Comparing to fields in Set Analysis

vinieme12 — Fri, 15 Sep 2017 08:01:26 GMT

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)

Re: Comparing to fields in Set Analysis

sebastianlettner — Fri, 15 Sep 2017 08:16:36 GMT

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

Re: Comparing to fields in Set Analysis

andrei_delta — Sun, 17 Sep 2017 13:52:02 GMT

Hello,

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

The result:

Hope it hels,

Andrei

Re: Comparing to fields in Set Analysis

effinty2112 — Sun, 17 Sep 2017 14:15:34 GMT

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

Re: Comparing to fields in Set Analysis

effinty2112 — Sun, 17 Sep 2017 14:17:00 GMT

Hi Mario,

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

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

Regards

Andrew

Re: Comparing to fields in Set Analysis

vinieme12 — Sun, 17 Sep 2017 14:26:34 GMT

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

];

Re: Comparing to fields in Set Analysis

effinty2112 — Sun, 17 Sep 2017 14:35:11 GMT

Hi Vineeth,

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

Thanks

Andrew