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

Correct way of mapping in a table with aggregate functions

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

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:

Test2:

Error:

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

vishsaggi — Wed, 19 Jul 2017 18:43:13 GMT

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);

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

oscar_ortiz — Wed, 19 Jul 2017 18:50:14 GMT

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

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

ziadm — Wed, 19 Jul 2017 19:38:24 GMT

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)

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

effinty2112 — Wed, 19 Jul 2017 19:51:10 GMT

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

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

manas_bn — Wed, 19 Jul 2017 19:51:15 GMT

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

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

julioarriaga — Wed, 19 Jul 2017 20:28:34 GMT

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

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

julioarriaga — Wed, 19 Jul 2017 21:15:23 GMT

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