Need to subtract within the field

[vardhan1305]

Hi,

i have a situation where i need to subtract within the table and create a new field(Amount).

i should get a new field by subtracting  IF - ST as  Amount .  can you please help me out

ID      Type    Value

1          ST      30

2          ST      10

3          DP      15

4          IF        20

5          OP        5

6            DP        32

[vishsaggi]

What is the expected output you are looking for?

[vardhan1305]

Hi Vishwanath,

ID      Type    Value   Amount

1          ST      30         -10

2          ST      10         10

3          DP      15        

4          IF        20         

5          OP        5

6            DP        32

[vishsaggi]

Try this?

LOAD * INLINE [

ID, Type, Value

1, ST,  30

2, ST,  10

3, DP,  15

4, IF,  20

5, OP,  5

6, DP,  32

];

SET vValue = "=Sum({<Type = {'IF'}>}Value)";

Then using Straight table add Dim

ID, Type

Expr: = IF(Match(Type, 'ST'), $(vValue) - Value)

[sergio0592]

Hi, try with :

if(Type='ST',sum({<Type={"IF"}>} TOTAL Value) -  sum({<Type={"ST"}>} Value))

[balabhaskarqlik]

May be this:

ABC:

Load

Id, Type,Value

from ABC;

Let vType = if(Type = "IF",Value);

Noconcatenate

Temp:

Load

     *,

If(match(Type,'ST'),$(vType)-Value,0) as Amount

Resident ABC;

Drop table ABC;

[manoj217]

if(Type='ST',Value,0) as ST

if(Type='IF',Value,0) as IF

IF-ST as amount