Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcement: Certain actions are currently causing a Page Not Found error. This is a known issue and we are working with the platform vendor to investigate and resolve it.
janani83
New Contributor II

Applymap and Aggregate functions

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.

Tags (1)
2 Replies
vishsaggi
Esteemed Contributor III

Re: Applymap and Aggregate functions

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;

MVP & Luminary
MVP & Luminary

Re: Applymap and Aggregate functions

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