Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tom_tierney
Contributor
Contributor

Sum values when field equals

All,

I have the following data; (Assume blank field values are Nulls)

IDTypeValue TypeValue
11
12
13
1210
1315
1220
22
24
225
2210
35
3210

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?

1 Solution

Accepted Solutions
Not applicable

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

View solution in original post

9 Replies
Not applicable

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

tom_tierney
Contributor
Contributor
Author

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

Not applicable

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?

tom_tierney
Contributor
Contributor
Author

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.

Not applicable

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

simenkg
Specialist
Specialist

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;

Not applicable

Hi,

I have modified my first comment

Please check it its working perfectly

Thanks

tom_tierney
Contributor
Contributor
Author

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.

PuriVelasco
Creator
Creator

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!!