SQL exception with calculated field

Question & Answer
,
1

Hey everyone
I am trying to create a calculated field but I always encounter an error. The calculation is the following:
ifelse(
type= “BATTERY”,
({usage_counter}/1000) / 1500,
({usage_counter}/1000) / 1800
)

I have a field called usage counter, I divide it by 1000 and they if the type is “BATTERY” by another 1500 and if the type is something else, it divided by 1800.
The idea is to underhand how much of my battery is used.

The calculation goes fine but when dropping it in a visual I get the following error:

image
image768×309 11.6 KB

image

Any ideas?

Thanks,

Etai

2

Hello @Etai,

What database are you using for your data source?

Kind regards,
Andres.

3

RDS,

Our data is there.

Hope it helps…

4

Hi, what RDS, PostgreSQL, MySQL? What type is the data in the field {usage_counter}? What type of visual are you using?

5

Hi,
Postgres and the the data in the field is an int.
it does not work in a table or a bar chart.

6

Hi @Etai - Is it possible to share sample data so that we can test it and provide the syntax.

Regards - Sanjeeb

7

Hey @Sanjeeb2022 ,
I eventually succeeded so thanks anyway :slight_smile:
Etai

2 Likes
8

Thanks @Etai . You can resolve this post.

Regards - Sanjeeb

9

Hi @Etai - Sorry to connect with you again, but what is the resolution step for your issue, can you please share to community as some users are also facing the same issue.

Regards - Sanjeeb

1 Like
10

Hi @Sanjeeb2022 ,

You are right, my apologies.

The previous calculation was a mistake and I presume that’s what caused the error.
I’ll explain. I have a type number which is associated with a type of battery. 18 for regular and 27 for high voltage (and there are other numbers for other parts. each one of them requires a different number to divide to (18 to 1000/1500 and 27 to 1000/1800).
What I did is an if else to give them a name (“Battery” or “Other parts”) and then tried to perform the calculation (on the already calculated field). I don’t know if that was the reason but something went wrong.
So, I went a step backwards and did the following:
ifelse(
type = 18,
({usage_counter} / 1000) / 1500,
ifelse(
type = 27,
({usage_counter} / 1000) / 1800,
null
)
)

This one worked :slight_smile:

Hope it help…

Etai

2 Likes
11

Thank you @Etai … Instead of referring the variable name, you are checking with value which helped in calculating the details in if else block.

Good to know this.

Regards - Sanjeeb