Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jacqueshol
Creator
Creator

Doing a calculation in the load script

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

 

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

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

View solution in original post

2 Replies
rubenmarin

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
Creator
Creator
Author

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