topic Applymap and Aggregate functions in QlikView

Applymap and Aggregate functions

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Applymap and Aggregate functions

vishsaggi — Thu, 19 Jul 2018 20:36:24 GMT

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;

Re: Applymap and Aggregate functions

marcus_sommer — Fri, 20 Jul 2018 14:45:37 GMT

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