
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Subtract during load?
I wonder if something like this is possible. If I have data like this:
UNITS: Load * Inline [
Month, Return, Units
10, , 5
10, , 5
10, x, 3
11, , 2
11, , 2
];
I should subtract the values where Return=x.
Load
Month,
Sum(Units, where Return ='') - Sum(Units, where Return=x) as Units // <- I'm making this up
Resident UNITS;
And the result would be
Month, Units
10, 7
11, 4
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I actually stated the problem wrongly, but thanks anyway, the IF statement was what I needed. What I ended up doing was just flipping the number to negative if Return=X, and after that all the sum expressions started working correctly.
if(Return='x',-Units, Units)) as Units

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hI, @alenb ,!, very fun load, check this please :
UNITS:
Load * Inline [
Month, Return, Units
10, , 5
10, , 5
10, x, 3
11, , 2
11, , 2
];
Load
recno() as Id,
Month,
Return,
Units,
if(rowno()=1, Units,if(Return='x',-Units, Units)) as UnitsForSum
Resident UNITS;
drop table UNITS;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @alenb
Load Month, Units, If(Len(Trim(Return))=0, Units, -Units) as Return Inline [
Month, Return, Units
10, , 5
10, , 5
10, x, 3
11, , 2
11, , 2
];
Dim: Month
Measure: Sum(Return) , Sum(Units)
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I actually stated the problem wrongly, but thanks anyway, the IF statement was what I needed. What I ended up doing was just flipping the number to negative if Return=X, and after that all the sum expressions started working correctly.
if(Return='x',-Units, Units)) as Units

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
it´s amazing when you start to understand how qlik works on loading, first you could think a complex formula to solve it, but after look at the data in qlik, you just create perfect and simple formulas like you did.
