Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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