Calculated Value in Script Editor

[Qlik_Nir]

Dear all,

I would like to create a new field value  in Backend as A-B 

A:
Load * Inline
[
Ledger,Category,Value
A,Internal,100
B,Internal,500
A,Cash,200
B,Cash,500
];

Desired Result would be 

Ledger,Category,Value
A,Internal,100
B,Internal,500

A-B,Internal,-400
A,Cash,200
B,Cash,500

A-B,Cash,-300

[Mark_Little · Accepted solution]

Something like

NoConcatenate 

TEMP1:

LOAD 

'A-B' as Ledger

Category,

Value

Resident A

Where Ledger = 'A'

Left Join  (TEMP1)

LOAD 

Category,

Value AS ValueB

Resident A

Where Ledger = 'A'

Concatenate (A)

Load

Ledger,

Category,

Value - ValueB as Value

Resident TEMP1;

Drop Table TEMP1;

[Qlik_Nir]

Thanks Mark,  it worked with few changes 

NoConcatenate

TEMP1:

LOAD

'A-B' as Ledger,

Category,

Value

Resident A

Where Ledger = 'A';

Left Join (TEMP1)

LOAD

Category,

Value AS ValueB

Resident A

Where Ledger = 'B';

Concatenate (A)

Load

Ledger,

Category,

Value - ValueB as Value

Resident TEMP1;

Drop Table TEMP1;

[Aasir]

// Load your initial data
OriginalData:
Load * Inline
[
Ledger, Category, Value
A, Internal, 100
B, Internal, 500
A, Cash, 200
B, Cash, 500
];

// Load data with the new field "A-B" and calculate the difference
NewData:
Load *,
If(Category = 'Internal', 'A-B', Category) as NewCategory,
If(Category = 'Internal', Sum(Value) - Peek('Value'), Value) as NewValue
Resident OriginalData
Order by Ledger, Category;

Drop Table OriginalData; // Optionally, you can drop the original table

// Rename the NewCategory field back to "Category" if needed
Rename Field NewCategory to Category;