logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
Tom Van Overbeke  
#1 Posted : Saturday, November 16, 2019 5:08:27 PM(UTC)
Tom Van Overbeke

Rank: Member

Groups: Registered
Joined: 10/23/2019(UTC)
Posts: 11
Belgium

Hello,

I'm using an excel file as my source, and I am trying to calculate the profit margin, by dividing 2 fields.
This works, but for some records, the divisor is a null, which leads to a divison by zero.

How can I circumvent this, and return a NULL value for the operation?

I know of the nullif function, but this it not accepted by the excel odbc driver. It looks like only very simple text functions are allowed by the excel odbc driver (I'm wondering if there exist a lists of which functions you can use ?)

This is my formula:

([Sheet1$].[base_sales_price-LAT_BE-EUR] - [Sheet1$].[purchase_price-EUR]) / [Sheet1$].[purchase_price-EUR] * 100


Tom Van Overbeke  
#2 Posted : Sunday, November 17, 2019 7:37:53 PM(UTC)
Tom Van Overbeke

Rank: Member

Groups: Registered
Joined: 10/23/2019(UTC)
Posts: 11
Belgium

Hi,

As a side question, I would like to know if it's possible to change data that is loaded from an excel file?
I know there are restrictions in manipulating files that are loaded by the odbc excel drivers, so I was wondering if I can use the 'Pre load script', 'Post load script' and 'Final script' in this case.

I gave it a try, but then it threw an error and I'm not sure if it's because of my inexperience with the razor scripting file, or that it's just not allowed in this case.


Example (see my previous post). I know that there are empty values for certain fields. I'm using this field in a division operation, and have encountered division by zero.

So if I could spot these records, and change the value to let's say -1, then the report would execute fine, and I would still see the records that have been changed because they would all have the value '-1'.

I'd like to hear if this is at all possible, and if yes, maybe a simple example of how to change a value of a field (if the value is null, then let the value be -1).

Thanks in advance,
Tom
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.