Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_Nir
Contributor
Contributor

Calculated Value in Script Editor

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

Labels (2)
1 Solution

Accepted Solutions
Mark_Little
Luminary
Luminary

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;

View solution in original post

3 Replies
Mark_Little
Luminary
Luminary

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
Contributor
Contributor
Author

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
Creator III
Creator III

// 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;