Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luisccmm
Creator
Creator

Applymap error

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

 

Labels (3)
1 Solution

Accepted Solutions
luisccmm
Creator
Creator
Author

Thank you @Anil_Babu_Samineni 

I have came accroos right now with the solution .

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

 

 

View solution in original post

6 Replies
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

 

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
luisccmm
Creator
Creator
Author

@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;	
Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
luisccmm
Creator
Creator
Author

@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" ;

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
luisccmm
Creator
Creator
Author

Thank you @Anil_Babu_Samineni 

I have came accroos right now with the solution .

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