Using Rangesum() on a single field?

[alexdataiq]

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

[tresesco]

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

[jagan]

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.

[tresesco · Accepted solution]

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

[jonathandienst]

Or you could simply do:

LOAD

     ID,

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

Resident ...

EDIT: beaten to the punch by tresesco

[jonathandienst]

Or a slightly more robust version:

LOAD

     ID,

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

...

[smirkinaa]

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.

[alexdataiq]

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!

[alexdataiq]

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.

[alexdataiq]

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