topic Re: Using Rangesum() on a single field? in QlikView

Using Rangesum() on a single field?

alexdataiq — Thu, 11 Feb 2016 00:08:22 GMT

Hi everybody!

It's posible to use Rangesum() on a single filed that contains comma separated values?

E.g.:

Having this values:

ID	Value
ABC,	50,30
EFG,	10,30,20

Use:

LOAD

ID,

Rangesum(Value) as NewValue

Resident...

To get

ID	NewValue
ABC	80
EFG	60

I've tried but the Sum always is zero!

Any ideas?

Regards

Re: Using Rangesum() on a single field?

tresB — Thu, 11 Feb 2016 08:58:30 GMT

You should rather use SubField() to get one one full record for each comma separated value. Try like:

Load

ID,

SubField(Value, ',') as Value

From <>;

This will give a table like

ID Value

ABC 50

ABC 30

EFG 10

EFG 20

EFG 30

Then you can sum them up in the back-end or at the front end

Re: Using Rangesum() on a single field?

jagan — Thu, 11 Feb 2016 09:04:37 GMT

Hi,

Rangesum() works when you have a static number of values, but in your case the number of values are different for each row.

The best solution is to use SubField() like below

Temp:

LOAD

ID,

SubField(Value, ',') AS Value

FROM DataSource;

Data:

Noconcatenate

LOAD

ID,

Sum(Value) AS Value

RESIDENT Temp

Group by ID;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

Re: Using Rangesum() on a single field?

tresB — Thu, 11 Feb 2016 09:11:39 GMT

Or, for this specific scenario you could try a rather tricky solution like:

LOAD

ID,

Evaluate(Replace(Value, ',' , '+')) as NewValue

Resident...

Edit: Corrected a brace

Re: Using Rangesum() on a single field?

jonathandienst — Thu, 11 Feb 2016 09:25:32 GMT

Or you could simply do:

LOAD

ID,

Evaluate(Replace(Value, ',', '+')) As NewValue

Resident ...

EDIT: beaten to the punch by tresesco

Re: Using Rangesum() on a single field?

jonathandienst — Thu, 11 Feb 2016 09:29:48 GMT

Or a slightly more robust version:

LOAD

ID,

Evaluate('(' & Replace(Value, ',', ')+(') & ')') As NewValue

...

Re: Using Rangesum() on a single field?

smirkinaa — Thu, 11 Feb 2016 10:45:30 GMT

Hello, may be this help you

LOAD

Evaluate('RangeSum(' & ABC & ')') as ABC,

Evaluate('RangeSum(' & EFG & ')') as EFG

FROM

...

Unfortunately, i can't understand how give value of field to function "RangeSum" like list of parameters.

Re: Using Rangesum() on a single field?

alexdataiq — Thu, 11 Feb 2016 17:33:36 GMT

This is exactly the kind of trick that I wanted! So far works like a charm!

Initially I was using SubField to create multiple records and then using Group By to get the product of the value of a field, but this is neater.

Thank you very much!

Re: Using Rangesum() on a single field?

alexdataiq — Thu, 11 Feb 2016 17:36:41 GMT

Yeah, this Is exactly how I was doing it but instead of Sum I was using another formula to get the product of the values of the field in question. But since my table had tons of fields it was rather cumbersome to add each field to the GroupBy. Besides grouping is expensive operation in my short experience.

That's why I wanted someting more concise (and elegant) like using RangeSum on a single field.

Regards.

Re: Using Rangesum() on a single field?

alexdataiq — Thu, 11 Feb 2016 17:38:05 GMT

Hehe, good answer! tresesco beated you for a few minutes