Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Partner
Partner

Using Rangesum() on a single field?

Hi everybody!

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

E.g.:

Having this values:

IDValue
ABC,50,30
EFG,10,30,20

Use:

LOAD

     ID,

     Rangesum(Value) as NewValue

Resident...

To get  

IDNewValue
ABC80
EFG60

I've tried but the Sum always is zero!

Any ideas?

Regards

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Using Rangesum() on a single field?

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

View solution in original post

9 Replies
MVP
MVP

Re: Using Rangesum() on a single field?

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

MVP & Luminary
MVP & Luminary

Re: Using Rangesum() on a single field?

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.

MVP
MVP

Re: Using Rangesum() on a single field?

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

View solution in original post

MVP
MVP

Re: Using Rangesum() on a single field?

Or you could simply do:

LOAD

     ID,

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

Resident ...

EDIT: beaten to the punch by tresesco

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Using Rangesum() on a single field?

Or a slightly more robust version:

LOAD

     ID,

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

...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
smirkinaa
New Contributor III

Re: Using Rangesum() on a single field?

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.

Partner
Partner

Re: Using Rangesum() on a single field?

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!

Partner
Partner

Re: Using Rangesum() on a single field?

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.

Partner
Partner

Re: Using Rangesum() on a single field?

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