Sum Function in Load Statement

[#1]

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

[tresesco]

Try something like:

Load

     Sum(Afgenomen?) as SomAfgenomen

From <> Group By OPA_OPDRNR;

[marcus_sommer]

Try this:

Sum([Afgenomen?]) AS SomAfgenomen

- Marcus

[#4]

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]

Hi Michel, i am not sure about your expersion

I will write syntax

load

  sum(field)

from table group by field.....

Regards

Nihhal.

[fsimoes81]

Afgenomen_Result:

LOAD

    Afgenomen?,

    OPA_OPDRNR,

    Sum(Afgenomen?)    as SumAfgenomen

Resident Afgenomen_tmp

GROUP BY Afgenomen?, OPA_OPDRNR;

[#7]

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]

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 · Accepted solution]

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

[#10]

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