Adding columns values while load

[#1]

Hello,

I have a excel file that has 3 value fields (columns) A, B and C that I want to add to get another column D.

"LOAD A + B + C as D" doesn't work somehow. I can of course create the D column in the source file while adding A, B and C, but is there a way I can sum A, B and C while loading?

thanks,

Manoj

[Anonymous]

Hi,

Make sure your script like this.

table1:

Load *, (A+B+C) as D;

Load *

FROM

(ooxml, embedded labels, table is Sheet1);

this should do.

BR,

chinna

[#3]

Hi Chinna,

I did try preceding load but it did not work either. Let me try again and check.

thanks,

Manoj

[#4 · Accepted solution]

try it

rangesum(a,b,c) as d

[#5]

Hi,

Use numsum(A+B+C) as D

or else try to use resident load.

table1:

Load *

FROM

(ooxml, embedded labels, table is Sheet1);

Table2:

Load *,

         A+B+C as D,

         Numsum(A+B+C) as D1

Resident table1;

Drop table1;

[#6]

That works. Thanks.

Manoj

[MarcoWedel]

very good.

So the trick was to treat null and non-numeric values as 0, like the range functions do.

regards

Marco