Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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;
// 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;