topic Re: Sum Function in Load Statement in QlikView

Sum Function in Load Statement

Wed, 28 Aug 2013 13:08:52 GMT

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

Re: Sum Function in Load Statement

tresB — Wed, 28 Aug 2013 13:14:20 GMT

Try something like:

Load

Sum(Afgenomen?) as SomAfgenomen

From <> Group By OPA_OPDRNR;

Re: Sum Function in Load Statement

marcus_sommer — Wed, 28 Aug 2013 13:18:47 GMT

Try this:

Sum([Afgenomen?]) AS SomAfgenomen

- Marcus

Re: Sum Function in Load Statement

Wed, 28 Aug 2013 13:41:11 GMT

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 C 1 6

1 A 3 6

Re: Sum Function in Load Statement

nihhalmca — Wed, 28 Aug 2013 13:43:32 GMT

Hi Michel, i am not sure about your expersion

I will write syntax

load

sum(field)

from table group by field.....

Regards

Nihhal.

Re: Sum Function in Load Statement

fsimoes81 — Wed, 28 Aug 2013 14:06:41 GMT

Afgenomen_Result:

LOAD

Afgenomen?,

OPA_OPDRNR,

Sum(Afgenomen?) as SumAfgenomen

Resident Afgenomen_tmp

GROUP BY Afgenomen?, OPA_OPDRNR;

Re: Re: Sum Function in Load Statement

Wed, 28 Aug 2013 14:34:27 GMT

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

Re: Sum Function in Load Statement

abeyphilip — Wed, 28 Aug 2013 14:49:53 GMT

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

Re: Sum Function in Load Statement

marcus_sommer — Wed, 28 Aug 2013 14:50:22 GMT

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

Re: Sum Function in Load Statement

Thu, 29 Aug 2013 07:50:16 GMT

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