Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
[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" ;
Thank you @Anil_Babu_Samineni
I have came accroos right now with the solution .
sum(Production*ApplyMap('Conversion_rates_mapping', Action)) as Result_1
Hello, Try this? Please try to remove the Sum() and then check what it is returning
ApplyMap('Conversion_rates_mapping', Action) as Result_1
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.
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;
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" ;
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" ;
Thank you @Anil_Babu_Samineni
I have came accroos right now with the solution .
sum(Production*ApplyMap('Conversion_rates_mapping', Action)) as Result_1