Load Script Help

[#1]

Hi guys,

I have a problem with my load script. I am trying to load values from a text file and do a multiplication of three fields and then a sum of the result as shown below:

LOAD

MONTH,

COMPANY,
BAL1,
BAL2
BAL3,
SUM (BAL1*BAL2*BAL3) AS TOTAL_BAL

FROM

...............

When I run this, my reload fails and I know it definitely has something to do with the multiplication I am doing to calculate the TOTAL_BAL.

Can someone please help me out with what I need to do to get this working?

Thank you

[#2 · Accepted solution]

In a load script, SUM() is a summary fucntion and requires a 'GROUP BY' clause to calculate. If I understand your requirement, you aren't looking for a sum at all, but just the multiplication.

If you take the SUM() out (leaveing the BAL1*BAL2*BAL3), it should work as you expect.

[#3]

Hi Ryan,

Thanks for your response. I took out the sum and the multiplication works fine, but I still want to sum the result of the product.

Say for example I had three numbers

(2*3*4) = 24 in one row of data and I had

(3*4*5) = 60 in another row

I will like to do both the multiplication and then do a sum for all the rows in one go.

Is that possible?

Or that I need to possibly do the multiplication and then do a resident load to do the sum of the product.

Thanks

[#4]

Hi Ryan,

Try the following in your script .It should work.

tab1:

LOAD

MONTH,

COMPANY,
BAL1,
BAL2
BAL3,
SUM (BAL1*BAL2*BAL3) AS TOTAL_BAL

FROM

...............

load sum(MUL) as TOTAL_BAL;

load (BAL1*BAL2*BAL3)as MUL resident tab1;

Regards

Tauqueer

[#5]

Hi Ryan,

Make the following modification in the previous script.

tab1:

LOAD

MONTH,

COMPANY,
BAL1,
BAL2,
BAL3

FROM

...............

load sum(MUL) as TOTAL_BAL;

load (BAL1*BAL2*BAL3)as MUL resident tab1;

Regards

Tauqueer