topic Re: How to sum an "applymap"? in App Development

How to sum an "applymap"?

Fri, 18 Aug 2017 07:29:40 GMT

Hi all,

I have a mapping in a table (A) whichs searches for values (1 or 0) in another table (B). The relationship between the two tables is a one-to-many.

What I want to show in table A is the existence of a "1" in one of the related rows in table B. To do that I want to sum the results in table B and I though that would be able by using "sum" in combination with a mapping field in table A. So I wrote this:

sum(Applymap('$(vApplyMap1)',%ShippingOrder,'0'))

However, on load the script returns an error: "invalid expression".

When I remvove the sum function but keep the rest of the mapping field intact, the script does not return an error (but is doesn't return the result I want either, of course).

Does anyone knows why I get an error when I try to apply the sum function to a mapping field?

Thank you!

Re: How to sum an "applymap"?

Anonymous — Fri, 18 Aug 2017 07:46:30 GMT

I think may have to do the sum in the Mapping Load along with a Group By.

Re: How to sum an "applymap"?

tore_arneson — Fri, 18 Aug 2017 07:48:21 GMT

Your general idea is sound. However, there is probably some issue with the variable you're passing. This should just be the name of a mapping table, loading using "Mapping LOAD".

Re: How to sum an "applymap"?

kaanerisen — Fri, 18 Aug 2017 08:16:46 GMT

That's odd if there is not a syntax error. But alternatively you can use "preceding load" to get desired results.

Example:

Load

id,

sum(Value) as Value

group by id;

load

id,

ApplyMap('MapTable',id) as Value;

sql select id from source

Re: How to sum an "applymap"?

Fri, 25 Aug 2017 07:09:16 GMT

Thank you all for your comments. I actually haven't made it work like i would've liked, but I've found a work-around.