Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

tom_tierney
New 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?

Tags (1)
1 Solution

Accepted Solutions
Not applicable

Re: Sum values when field equals

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

8 Replies
Not applicable

Re: Sum values when field equals

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
New Contributor

Re: Sum values when field equals

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

Re: Sum values when field equals

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
New Contributor

Re: Sum values when field equals

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

Re: Sum values when field equals

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

bwisenosimenkg
Valued Contributor

Re: Sum values when field equals

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

Re: Sum values when field equals

Hi,

I have modified my first comment

Please check it its working perfectly

Thanks

tom_tierney
New Contributor

Re: Sum values when field equals

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.

Community Browser