Applymap and Aggregate functions

[Anonymous]

Is it possible to do group by in the mapping load?

Example:

UnitPriceMap:

Mapping LOAD

Plant & Material & Month([Posting Date]) & Year([Posting Date])& [Document currency] as UnitPriceKey,

sum([Amount in Doc Curr])/sum([Quantity]) as UnitPriceDocCurrencyNew

Resident Temp_Spend

Group By Plant , Material , Month([Posting Date]), Year([Posting Date]), [Document currency];

Spend:

LOAD *,

ApplyMap('UnitPriceMap',Plant & Material & Month([Posting Date])& Year([Posting Date]) & [Document currency])as UnitPrice_Monthly,

Currently it is pulling the concatenation as UnitPrice_Monthly.

[vishsaggi]

Are you not getting any values in your UnitPrice_Monthly? May be try like:

UnitPriceMap:

MAPPING LOAD *;

LOAD

Plant & Material & Month([Posting Date]) & Year([Posting Date])& [Document currency] as UnitPriceKey,

sum([Amount in Doc Curr])/sum([Quantity]) as UnitPriceDocCurrencyNew

Resident Temp_Spend

Group By Plant , Material , Month([Posting Date]), Year([Posting Date]), [Document currency];

Spend:

LOAD *, ApplyMap('UnitPriceMap',UnitPriceKey)as UnitPrice_Monthly;

LOAD *,

     Plant & Material & Month([Posting Date])& Year([Posting Date]) & [Document currency] AS UnitPriceKey,

     Yourotherfieldshere

From Source;

[marcus_sommer]

It looks like that there are no matching values between both tables - maybe an issue with the formatting or the data-quality by one or several fields - and because you didn't specify the third parameter (for returning a default-value if no matching value is available) the lookup-value is returned.

- Marcus