Sum of Multiple fields into one while loading

[#1]

Hi,

Below load script fails to sum up all the values

Load

residentialunitprice+maintenancefundprice+totalcustomizationcharges+totalservicetax+totalvat+wc as S_TotalCharges

FROM

[$(vPath)\tesaleorde.qvd]

(qvd);

But when i load all the above fields separately and sum up it shows correct value.

Please help me with this.

Regards,

Keerthi KS

[jagan]

Hi,

Can you attach the sample data, it seems the script is correct.

OR else try Rangesum() like below

Load

Rangesum(residentialunitprice, maintenancefundprice, totalcustomizationcharges, totalservicetax, totalvat, wc) as S_TotalCharges

FROM

[$(vPath)\tesaleorde.qvd]

(qvd);

Also make sure that all column name are correct, also if there is any null values you won't get expected value, but with RangeSum() you will get the result correctly.

Regards,

Jagan.

[#3]

Column names are correct but some values will be 0 in some cases.

Rangesum will solve this issue?

[jagan]

Think so, I think you have some null values or some non numeric values.  Try Rangesum, it should work.

Regards,

Jagan.

[krishna20]

Hi ,

Try this,

Trim(residentialunitprice)+Trim(maintenancefundprice)+Trim(totalcustomizationcharges)+Trim(totalservicetax)+Trim(totalvat)+Trim(wc) as S_TotalCharges

Regards

Krishna

[#6]

Hi Jagan,

Its not working in my case. I have attached the sample data.

Please help me with this.

Regards,

Keerthi KS

[#7]

I am not getting the correct result. It results Zero

[sasiparupudi1]

Hi Keerthi

You need to have a group by clause and aggregation function in your load statement. Use like below

LOAD

     uniqueid,

     sum(residentialunitprice) as residentialunitprice,

     sum(maintenancefundprice) as maintenancefundprice,

     sum(totalcustomizationcharges) as totalcustomizationcharges,

     sum(totalservicetax) as totalservicetax,

     sum(totalvat) as totalvat,

     sum(wc) as wc,

     sum(residentialunitprice+maintenancefundprice+totalcustomizationcharges+totalservicetax+totalvat+wc) as S_TotalCharges

     //residentialunitprice+maintenancefundprice+totalcustomizationcharges+totalservicetax+totalvat+wc as totalcharges

FROM C:\sasi\qv\tesaleorde.qvd(qvd)

group by uniqueid;

hth

Sasi

[jagan · Accepted solution]

Hi,

Try this it is working

LOAD

     uniqueid,

     residentialunitprice,

     maintenancefundprice,

     totalcustomizationcharges,

     totalservicetax,

     totalvat,

     wc,

    Rangesum(residentialunitprice, maintenancefundprice, totalcustomizationcharges, totalservicetax, totalvat, wc) as S_TotalCharges,

     residentialunitprice+maintenancefundprice+totalcustomizationcharges+totalservicetax+totalvat+wc as totalcharges

FROM

[tesaleorde.qvd]

(qvd);

In RangeSum() you have give comma not + symbol.

Regards,

Jagan.

[krishna20]

Hi,

Try this

RangeSum(residentialunitprice)+RangeSum(maintenancefundprice)+RangeSum(totalcustomizationcharges)+RangeSum(totalservicetax)+RangeSum(totalvat)+RangeSum(wc) as totalcharges

Regards

Krishna