Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I have the following data; (Assume blank field values are Nulls)
ID | Type | Value Type | Value |
---|---|---|---|
1 | 1 | ||
1 | 2 | ||
1 | 3 | ||
1 | 2 | 10 | |
1 | 3 | 15 | |
1 | 2 | 20 | |
2 | 2 | ||
2 | 4 | ||
2 | 2 | 5 | |
2 | 2 | 10 | |
3 | 5 | ||
3 | 2 | 10 |
I want to sum the Value field if the ID has Type = 1 or 2. (The 'Value Type' can be ignored)
So the result would be;
ID 1 = 45, ID 2 = 15, ID 3 = 0
Ideally I would want to accomplish this in the Load Script and not at the object stage (Set Analysis etc.)
The real data is in a single DB view and has approx. 1M rows and many more fields. What is the best approach in terms of optimisation? Mapping table/Cross table?
Hi,
Tab1:
LOAD ID,
Type,
[Value Type],
Value
FROM
[http://community.qlik.com/thread/128756]
(html, codepage is 1252, embedded labels, table is @1);
Load ID as CheckID
Resident Tab1
where Type=1 or Type=2;
Load ID,
Sum(if(Exists(CheckID,ID),Value,0)) as Value
Resident Tab1
Group By ID;
Drop Table Tab1;
Thanks and Regards,
Vivek
Hi,
Tab1:
LOAD ID,
Type,
[Value Type],
Value
FROM
[http://community.qlik.com/thread/128756]
(html, codepage is 1252, embedded labels, table is @1);
Load ID as CheckID
Resident Tab1
where Type=1 or Type=2;
Load ID,
Sum(if(Exists(CheckID,ID),Value,0)) as Value
Resident Tab1
Group By ID;
Drop Table Tab1;
Thanks and Regards,
Vivek
Vivek,
Thanks for the reply but this doesn't quite work.
No test is made to check if the Type = 1 or 2.
Therefore there is a result for ID 3 where the Type = 5.
Tom
Script approach:
LOAD
Only(ID),
Sum(Value)
WHERE Type=1 OR Type=2
GROUP BY ID
;
LOAD ID,
Type,
[Value Type],
Value
FROM
[test2.xlsx]
(ooxml, embedded labels, table is Blad1);
Set analysis:
Dimension: ID
Expression: =sum({$<Type={1,2}>} Value )
By the way, the table you pasted has no values matching the condition you described: (Type=1 or Type=2) And Value <> ''
Made a mistake there?
Jasper,
Again thanks for the response.
No there isn't a mistake. There are no Type values on the rows that have the amount Values.
On those rows we only have the ID.
We know that a given ID has a Type.
If the Type is 1 or 2 we need to Sum the values associated with the ID.
So the result I am looking for is;
ID Value
1 45
2 15
3 0 (Because ID 3 does not have a Type that equals 1 or 2)
Tom.
Hi Tom,
Sorry for the previous reply
I have modified it ,Please try it
Tab1:
LOAD ID,
Type,
[Value Type],
Value,
if(Type=1 or Type=2,Value,0) as Value1
FROM
[http://community.qlik.com/thread/128756]
(html, codepage is 1252, embedded labels, table is @1);
Load ID,
Sum(Value1) as Value
Resident Tab1
Group By ID;
Drop Table Tab1;
Note:
By the way, the table you pasted has no values matching the condition you described: (Type=1 or Type=2)
So for evrything it will give 0
Thanks and Regards,
Vivek
LOAD
distinct ID as ValidID
From Table1
WHERE Type=1 OR Type=2
;
LOAD
ID,
sum(Value)
FROM
Table1
where exists(ValidID,ID)
group by ID;
Hi,
I have modified my first comment
Please check it its working perfectly
Thanks
Vivek,
Thanks very much. This works perfectly.
I will now point it at the live data and see how this performs with 1M+ rows.
Tom.
Hello,
I have a similar question, but in my case I have to sum up the values from the ID = 1 + values for ID = 2, (if I have your example).
Anyone knows how I can do this?
Thank you very much!!