topic Re: Sum of Multiple fields into one while loading in QlikView

Sum of Multiple fields into one while loading

Thu, 03 Sep 2015 07:29:01 GMT

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

Re: Sum of Multiple fields into one while loading

jagan — Thu, 03 Sep 2015 07:34:07 GMT

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.

Re: Sum of Multiple fields into one while loading

Thu, 03 Sep 2015 07:38:57 GMT

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

Rangesum will solve this issue?

Re: Sum of Multiple fields into one while loading

jagan — Thu, 03 Sep 2015 07:39:51 GMT

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

Regards,

Jagan.

Re: Sum of Multiple fields into one while loading

krishna20 — Thu, 03 Sep 2015 07:47:04 GMT

Hi ,

Try this,

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

Regards

Krishna

Re: Sum of Multiple fields into one while loading

Thu, 03 Sep 2015 07:51:14 GMT

Hi Jagan,

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

Please help me with this.

Regards,

Keerthi KS

Re: Sum of Multiple fields into one while loading

Thu, 03 Sep 2015 07:53:56 GMT

I am not getting the correct result. It results Zero

Re: Sum of Multiple fields into one while loading

sasiparupudi1 — Thu, 03 Sep 2015 09:35:54 GMT

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

Re: Sum of Multiple fields into one while loading

jagan — Thu, 03 Sep 2015 09:55:36 GMT

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.

Re: Sum of Multiple fields into one while loading

krishna20 — Thu, 03 Sep 2015 10:12:43 GMT

Hi,

Try this

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

Regards

Krishna

Re: Sum of Multiple fields into one while loading

Thu, 03 Sep 2015 10:19:21 GMT

Currently i have used

if(IsNull(residentialunitprice),0,residentialunitprice)+if(IsNull(maintenancefundprice),0,maintenancefundprice)

+if(IsNull(totalcustomizationcharges),0,totalcustomizationcharges)+if(IsNull(totalservicetax),0,totalservicetax)

+if(IsNull(totalvat),0,totalvat)+if(IsNull(wc),0,wc) as S_TotalCharges

Re: Sum of Multiple fields into one while loading

jagan — Thu, 03 Sep 2015 10:21:40 GMT

Hi Keerthi,

Did you tried my solution? Rangesum() will do all this checks.

Regards,

Jagan.

Re: Sum of Multiple fields into one while loading

Thu, 03 Sep 2015 10:25:32 GMT

Hi Jagan,

Yes I have tried your approach and Its working.

I have used '+' instead of ',' so it was not working.

Thanks a lot.

Regards,

Keerthi

Re: Sum of Multiple fields into one while loading

jagan — Thu, 03 Sep 2015 10:30:30 GMT

Hi Keerthi,

If you got the answer please close this thread by giving correct and helpful answers to the helpful posts.

Regards,

jagan.