Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum Function in Load Statement

Hello,

This sum function works well in a chart but I would like to copy this to the LOAD script but that doesn't work.

Sum(Total<OPA_OPDRNR>Afgenomen?) AS SomAfgenomen,  // The ? is part of the field name

How do I calculate the total sum per OPA_OPDRNR in de LOAD statement?

Thanx for any help.

Michel van de Vosse

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

The brackets must be use by any special char but without it will also work. I Think you need:

Afgenomen_Result:

LOAD

    OPA_OPDRNR,

    Sum(Afgenomen?)    as SumAfgenomen

Resident Afgenomen_tmp

GROUP BY OPA_OPDRNR;

This table is automatically associated per OPA_OPDRNR as Key. Otherwise you could this data also insert per mapping to the maintable.

- Marcus

View solution in original post

9 Replies
tresesco
MVP
MVP

Try something like:

Load

     Sum(Afgenomen?) as SomAfgenomen

From <> Group By OPA_OPDRNR;

marcus_sommer
MVP & Luminary
MVP & Luminary

Try this:

Sum([Afgenomen?]) AS SomAfgenomen


- Marcus

Not applicable
Author

Hi,

It's not that I want to Group my result records, because that is what I get with a Group By,

I would like the sum of Afgenomen? for the different OPA_OPDRNR in each row.

Something like:

Afgenomen?      OPA_OPDRNR     SUM     WRONG SUM

1                         A                         3               6

1                         B                         2               6

1                         B                         2               6

1                         C                         1               6

1                         A                         3               6

1                         A                         3               6

nihhalmca
Specialist II
Specialist II

Hi Michel, i am not sure about your expersion

I will write syntax

load

  sum(field)

from table group by field.....

Regards

Nihhal.

fsimoes81
Partner
Partner

Afgenomen_Result:

LOAD

    Afgenomen?,

    OPA_OPDRNR,

    Sum(Afgenomen?)    as SumAfgenomen

Resident Afgenomen_tmp

GROUP BY Afgenomen?, OPA_OPDRNR;

Not applicable
Author

Hi Marcus,

Adding the [] wil tell Qlickview that there is a space in the field name right? But that's not the problem here. See my answer to Tresesco for additional info on what results I am looking for.

See also attatched xls with some test data.

Michel

abeyphilip
Creator II
Creator II

Hi Michel,

Use group by and get the sum that you want for each OPA_OPDRNR.

load OPA_OPDRNR,

sum(Afgenomen)

from x

group by OPA_OPDRNR;

And then join this with your main table, you will have the sum field as another field to your main table.

Regards,

Abey

marcus_sommer
MVP & Luminary
MVP & Luminary

The brackets must be use by any special char but without it will also work. I Think you need:

Afgenomen_Result:

LOAD

    OPA_OPDRNR,

    Sum(Afgenomen?)    as SumAfgenomen

Resident Afgenomen_tmp

GROUP BY OPA_OPDRNR;

This table is automatically associated per OPA_OPDRNR as Key. Otherwise you could this data also insert per mapping to the maintable.

- Marcus

View solution in original post

Not applicable
Author

Hi Marcus,

Thanx, Now it works, I have done it like this:

BerSomAfgenomen:

Join (OpaOndAssTMP)

LOAD

OPA_OPDRNR,

SUM(Afgenomen) AS SomAfgenomen

Resident OpaOndAssTMP

Group By OPA_OPDRNR;

I have repeated this for another field and then with a resident load on the main table I calculated onwards with the two  joined fields.

Michel