Doing a calculation in the load script

[jacqueshol]

Hi there,

I receive the following data via an API from Smartsheet as follows:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='R# ##0,00;-R# ##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD-MM-YY';
SET TimestampFormat='DD-MM-YY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

ODBC CONNECT TO [My Smartsheet] (XUserId is eRQXFVFM████████████, XPassword is ███████████);

LOAD
[Operation],
[Truck],
[Date],
[Shift],
[Status],
[Comments],
[Operator],
[Opening Kms],
[Closing Kms],
[Kms],
[Location 1],
[Loc 1 Loads],
[Loc 1 Tons],
[Location 2],
[Loc 2 Loads],
[Loc 2 Tons],
[Location 3],
[Loc 3 Loads],
[Loc 3 Tons],
//sum([Loc 1 Loads]) + sum([Loc 2 Loads]) + sum([Loc 3 Loads]) as [Total Loads],
[Time Out],
[Time Back];

SQL SELECT *
FROM "Daily Ops Report (VR) - Grid__5422264863745924s_(Phakamisa/Daily Ops Report for Vaal River)";

My script above runs 100% and I am able to import data just fine into my model. I, however, have to often work with the Total Loads which is a sum of [Loc 1 Loads] and [Loc 2 Loads] and [Loc 3 Loads]. Creating a new field in the script that sums this, would be great and save considerable time.

I tried adding a calculation in the SQL portion of the script, but that didn’t work. After reading a few posts on the forum, I added a LOAD section and added the calculation in there (green line in the script). This unfortuanately doesn’t work and I get an error message (see attached screenshot):

ODBC read failed

SQL SELECT *

FROM “Daily Ops Report (VR) - Grid__5422264863745924s_(Phakamisa/Daily Ops Report for Vaal River)";

When I change the calculation line to:

 [Loc 1 Loads] + [Loc 2 Loads] + [Loc 3 Loads] as [Total Loads],

the script runs fine without error, but [Total Loads] tallies to 0 when that is clearly not the correct answer.

I have searched comprehensively, but can’t figure out what I need to do. Can anyone assist please? Any help will be greatly appreciated.

Thanks,

Jacques

[rubenmarin · Accepted solution]

Hi, using +, if any of the fields is Null() the result will be Null(). 

Try with RangeSum: RangeSum([Loc 1 Loads],[Loc 2 Loads],[Loc 3 Loads]) as [Total Loads].

1 + Null() = Null() ; RangeSum(1, Null())=1

[jacqueshol]

Oh wow, that simple? I can't believe it 😀.

I just tried it and it worked a charm. Thank you so much. I wish I asked sooner, but the day or two struggling through it, had me learning a few things I haven't known before. 

Anyway, a kind thank you for your quick and excellent response.

Take care,

Jacques