Applymap error

[luisccmm]

I am having a problem with a SCRIPT function.

I am trying to make a table with the PRODUCTION from Action='Action1' and grouped by date. The function here (sum(Production)*ApplyMap('Conversion_rates_mapping', Action) as Result_1) tries to multiple production from Action1 to its conver_rate from table CONVERSION_RATES.

[DATE_PRODUCTION_2]:

LOAD "Date",

sum(Production)*ApplyMap('Conversion_rates_mapping', Action) as Result_1

Resident [DATE_PRODUCTION]

WHERE Action='Action1'

group by "Date"

Type of error:

Invalid expression

ORIGINAL SCRIPT

[CONVERSION_RATES]:
LOAD
	Action,
	Conver_rate
 FROM [lib://TEST APP/Conversion_rates.xlsx]
(ooxml, embedded labels, table is CONVERSION_RATES);

Conversion_rates_mapping:
Mapping LOAD
 Action, 
 Conver_rate
Resident CONVERSION_RATES;


[DATE_PRODUCTION]:
LOAD
    "Date",
    Action,
    Production   
 FROM [lib://TEST APP/Conversion_rates.xlsx]
(ooxml, embedded labels, table is DATE_PRODUCTION);


[DATE_PRODUCTION_2]:
LOAD
    "Date",
    sum(Production)*ApplyMap('Conversion_rates_mapping', Action) as Result_1
Resident [DATE_PRODUCTION]
WHERE Action='Action1'
group by "Date" ;	

[Anil_Babu_Samineni]

Hello, Try this? Please try to remove the Sum() and then check what it is returning

ApplyMap('Conversion_rates_mapping', Action) as Result_1

[luisccmm]

@Anil_Babu_Samineni 

If i do that,

I GET this error

Aggregation expressions required by GROUP BY clause.

Because that TABLE has a GROUP BY clause and need some aggregation function.

[Anil_Babu_Samineni]

My bad, This should be?

[DATE_PRODUCTION_2]:
LOAD
    "Date",
    ApplyMap('Conversion_rates_mapping', Action) as Result_1
Resident [DATE_PRODUCTION]
WHERE Action='Action1';	

 But, As per your script I can simply do this also

[DATE_PRODUCTION_2]:
LOAD
    "Date",
    Action,
    sum(Production)*Action as Result_1
Resident [DATE_PRODUCTION]
WHERE Action='Action1'
group by "Date", Action;	

[luisccmm]

@Anil_Babu_Samineni 

Thanks again for your answer, but I think there is a missunderstanding.

I am trying to multiple

Conver_rate     *   Sum(production)

Conver_rate comes from CONVER_RATE table that´s why I was trying to use APPLYMAP,

Multiplying the following has no sense because Action variable is a text string.

sum(Production)*Action

But as I said previously my script is not working with applymap.

[DATE_PRODUCTION_2]:
LOAD
    "Date",
    sum(Production)*ApplyMap('Conversion_rates_mapping', Action) as Result_1
Resident [DATE_PRODUCTION]
WHERE Action='Action1'
group by "Date" ;	

[Anil_Babu_Samineni]

Your script determining with the Production and Action. Where did you used Conver_rate? Perhaps this way

[CONVERSION_RATES]:
LOAD
Action,
Conver_rate
FROM [lib://TEST APP/Conversion_rates.xlsx]
(ooxml, embedded labels, table is CONVERSION_RATES);

Conversion_rates_mapping:
Mapping LOAD
Action,
Conver_rate
Resident CONVERSION_RATES;

[DATE_PRODUCTION]:
LOAD
"Date",
Action,
Production
FROM [lib://TEST APP/Conversion_rates.xlsx]
(ooxml, embedded labels, table is DATE_PRODUCTION);

[DATE_PRODUCTION_2]:
LOAD
"Date",
sum(Production)*ApplyMap('Conversion_rates_mapping', Conver_rate) as Action
Resident [DATE_PRODUCTION]
WHERE Action='Action1'
group by "Date" ;

[luisccmm · Accepted solution]

Thank you @Anil_Babu_Samineni 

I have came accroos right now with the solution .

sum(Production*ApplyMap('Conversion_rates_mapping', Action)) as Result_1