topic Re: Sum values when field equals in QlikView

Sum values when field equals

tom_tierney — Fri, 08 Aug 2014 07:39:19 GMT

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?

Re: Sum values when field equals

Fri, 08 Aug 2014 08:51:53 GMT

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

Re: Sum values when field equals

tom_tierney — Fri, 08 Aug 2014 09:13:59 GMT

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

Re: Sum values when field equals

Fri, 08 Aug 2014 09:25:39 GMT

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?

Re: Sum values when field equals

tom_tierney — Fri, 08 Aug 2014 10:16:57 GMT

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.

Re: Sum values when field equals

Fri, 08 Aug 2014 10:18:26 GMT

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

Re: Sum values when field equals

simenkg — Fri, 08 Aug 2014 10:27:44 GMT

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;

Re: Sum values when field equals

Fri, 08 Aug 2014 11:08:03 GMT

Hi,

I have modified my first comment

Please check it its working perfectly

Thanks

Re: Sum values when field equals

tom_tierney — Fri, 08 Aug 2014 11:20:05 GMT

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.

Re: Sum values when field equals

PuriVelasco — Fri, 04 Dec 2020 12:56:35 GMT

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