Correct way of mapping in a table with aggregate functions

[julioarriaga]

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:

[vishsaggi]

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

[oscar_ortiz]

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

[ziadm]

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)

[effinty2112]

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

[manas_bn · Accepted solution]

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

[julioarriaga]

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

[julioarriaga]

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