Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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.
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
Or you could simply do:
LOAD
ID,
Evaluate(Replace(Value, ',', '+')) As NewValue
Resident ...
EDIT: beaten to the punch by tresesco
Or a slightly more robust version:
LOAD
ID,
Evaluate('(' & Replace(Value, ',', ')+(') & ')') As NewValue
...
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.
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!
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.
Hehe, good answer! tresesco beated you for a few minutes