Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
julioarriaga
Contributor II

Correct way of mapping in a table with aggregate functions

Hi everyone,

Sorry for the basic question: I wrote the code (Test1) from a Binary and it worked fine. In Test2 I added a Sum(Total_Sales) grouped by Week and Fiscal_Year but it showed an error (Error image), saying that the field Year is not found. What is the correct way of doing a mapping in a table that has aggregate functions? Because I suspect that is causing the problem.

Thanks in advance

Test1:

Capture1.PNG

Test2:

Capture2.PNG

Error:

Capture3.PNG

1 Solution

Accepted Solutions
Highlighted
manas_bn
Contributor

Re: Correct way of mapping in a table with aggregate functions

In line 20, Instead of "group by Week,Year", try "group by Week, ApplyMap('map1',Week)"

View solution in original post

7 Replies
Highlighted
vishsaggi
Esteemed Contributor III

Re: Correct way of mapping in a table with aggregate functions

Try this?

LOAD Week, Year,

           Sum(Total_Sales) As Sales

Group by Week, Year;

LOAD Week,

ApplyMap('map1', Week) AS Year

Resident Fact

WHERE Week = '201701';

Store Sales into test.txt(txt);

Highlighted
Partner
Partner

Re: Correct way of mapping in a table with aggregate functions

Julio,

You should remove the Year field from the Group By clause.  Year does not yet exist as you are creating it with the Week field using your ApplyMap.

Good luck

Oscar

Highlighted
ziadm
Valued Contributor

Re: Correct way of mapping in a table with aggregate functions

Try This

LOAD Week,

ApplyMap('map1', Week) AS Year,

Sum(Total_Sales) As Sales

Resident Fact

WHERE Week = '201701'

Group By Week ,ApplyMap('map1', Week)

Highlighted
effinty2112
Honored Contributor

Re: Correct way of mapping in a table with aggregate functions

Hi Julio,

Why group on year or week when you have only one week of data?

Sales:

load Week,

applymap('map1') as Year,

sum(Total_Sales) as Total_Sales,

resident Fact

where Week = '201701';

Cheers

Andrew

Highlighted
manas_bn
Contributor

Re: Correct way of mapping in a table with aggregate functions

In line 20, Instead of "group by Week,Year", try "group by Week, ApplyMap('map1',Week)"

View solution in original post

Highlighted
julioarriaga
Contributor II

Re: Correct way of mapping in a table with aggregate functions

Ah it is just a sample. In production it wouldn't be filtered by a single week.

Highlighted
julioarriaga
Contributor II

Re: Correct way of mapping in a table with aggregate functions

Thanks, that was the problem, I was doing it with the alias.