Sum values when field equals

[tom_tierney]

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?

[#2 · Accepted solution]

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]

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

[#4]

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]

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.

[#6]

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]

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;

[#8]

Hi,

I have modified my first comment

Please check it its working perfectly

Thanks

[tom_tierney]

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]

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