topic Re: Expressions in Load in QlikView

Expressions in Load

Anonymous — Mon, 14 May 2018 19:47:35 GMT

Hi All.

I have been working on trying to do some expressions in my load script so I can access them in the objects instead of creating the expressions in the objects.

However I am running into failed execution errors and I can't figure out why it is happening. I've read about how to store and load from qvd files and I think I have it right but it just isn't working and I'm out of ideas on what to look at.

I may not be doing a good job with handling the data flow here but I'm trying to learn how to do this as it is a powerful method that I could employ regularly.

Below is the script I'm using.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//Begin custom variables

SET vScore = 100;

SET vStopMulti = 5;

SET vDistMulti = .5;

SET vMPGMulti = 1;

SET vTimeMulti = 1;

SET vMPGGoal = 6.8;

SET vLowDist = -4;

SET vHiDist = 3;

SET vPCTStopMulti = 1;

SET vPctDispDiv = 5;

//Begin Load of main tables

ODBC CONNECT32 TO database;

summdispatch:

LOAD

left(dispatch_number, 14) AS Dispatch,

mid(dispatch_number, 5, 10) AS FO,

Date(floor(actual_start,),'MM-DD-YYYY') AS DispStartDate,

driver,

vehicle_number AS vehicleNum,

stopsmade,

stopsmissed,

stopsmade-stopsmissed AS stopsVariance,

(plannedroutetime/60) AS plannedRouteTime,

(actualroutetime/60) AS actualRouteTime,

(plannedroutetime/60)-(actualroutetime/60) AS routeTimeVariance,

plannedservicetime,

actualservicetime,

(actualservicetime/60) AS actualServiceTime,

(planneddistance*.6214) AS plannedDistance,

actualdistance,

(planneddistance*.6214)-actualdistance AS distVariance,

plannedtraveltime,

actualtraveltime,

actualfuel,

mpg,

Today() AS TodayTest

;

SELECT *

FROM database.dbo."summdispatch";

STORE summdispatch INTO [path\summdispatch.qvd](qvd)

;

dispatchAnalysis:

LOAD

Plant As da.Plant,

Vehicle_Number AS da.vehicleNum,

Left(Dispatch_Number,14) AS da.Dispatch,

Freight_Order_Number AS da.FO,

Date(Floor(Dispatch_Begin_Time)) AS da.DispStartDate,

Stop_Number AS da.Stop_Number,

Date_Time_InAccurate AS da.DTInaccurate,

Driver AS da.driver

;

SELECT *

FROM database.dbo."vw_Dispatch_Analysis";

STORE dispatchAnalysis INTO [path\dispatchAnalysis.qvd](qvd)

;

LOAD *

FROM

[path\summdispatch.qvd](qvd)

;

daSum:

LOAD

da.VehicleNum AS vehicleNum,

da.DispStartDate AS DispStartDate,

da.driver As driver,

Sum(da.DTInacurate) As InAccStopSum

FROM [path\dispatchAnalysis.qvd](qvd)

GROUP BY

da.VehicleNum,

da.DispStartDate,

da.driver

;

Re: Expressions in Load

olivierrobin — Tue, 15 May 2018 07:13:57 GMT

hello

could you post the error log ?

Re: Expressions in Load

Miguel_Angel_Baeyens — Tue, 15 May 2018 07:20:18 GMT

The script looks all right at a first glance. But be aware that field names are case sensitive in QlikView, so if the driver returns "plant" and you use "Plant" the load will fail.

Also, you are loading twice summdispatch, once from the SELECT and the next from the QVD file, which is not needed, as the table summdispatch is already in memory.

Apart from that, which issues are you encountering?

Re: Expressions in Load

Anonymous — Mon, 21 May 2018 18:52:04 GMT

Sorry I thought I had replied to this message but apparently I did not.

I did find an issue with a field name which I corrected.

However I simply get a generic error that says "Execution of script failed. Reload old data?".

Below is the updated script.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//Begin custom variables

SET vScore = 100;

SET vStopMulti = 5;

SET vDistMulti = .5;

SET vMPGMulti = 1;

SET vTimeMulti = 1;

SET vMPGGoal = 6.8;

SET vLowDist = -4;

SET vHiDist = 3;

SET vPCTStopMulti = 1;

SET vPctDispDiv = 5;

//Begin Load of main tables

ODBC CONNECT32 TO PeopleNet;

summdispatch:

LOAD

left(dispatch_number, 14) AS Dispatch,

mid(dispatch_number, 5, 10) AS FO,

Date(floor(actual_start,),'MM-DD-YYYY') AS DispStartDate,

driver,

vehicle_number AS vehicleNum,

stopsmade,

stopsmissed,

stopsmade-stopsmissed AS stopsVariance,

(plannedroutetime/60) AS plannedRouteTime,

(actualroutetime/60) AS actualRouteTime,

(plannedroutetime/60)-(actualroutetime/60) AS routeTimeVariance,

plannedservicetime,

actualservicetime,

(actualservicetime/60) AS actualServiceTime,

(planneddistance*.6214) AS plannedDistance,

actualdistance,

(planneddistance*.6214)-actualdistance AS distVariance,

plannedtraveltime,

actualtraveltime,

actualfuel,

mpg,

Today() AS TodayTest

;

SELECT *

FROM PeopleNet.dbo."summdispatch";

STORE summdispatch INTO (qvd)

;

dispatchAnalysis:

LOAD

Plant As da.Plant,

Vehicle_Number AS da.vehicleNum,

Left(Dispatch_Number,14) AS da.Dispatch,

Freight_Order_Number AS da.FO,

Date(Floor(Dispatch_Begin_Time)) AS da.DispStartDate,

Stop_Number AS da.Stop_Number,

Date_Time_InAccurate AS da.DTInaccurate,

Driver AS da.driver

;

SELECT *

FROM PeopleNet.dbo."vw_Dispatch_Analysis";

STORE dispatchAnalysis INTO (qvd)

;

daSum:

LOAD

da.vehicleNum AS vehicleNum,

da.DispStartDate AS DispStartDate,

da.driver As driver,

Sum(da.DTInacurate) As InAccStopSum

FROM (qvd)

GROUP BY

da.vehicleNum,

da.DispStartDate,

da.driver

;

Re: Expressions in Load

Peter_Cammaert — Mon, 21 May 2018 19:46:07 GMT

Where do you get that error? At the very end of your script, or somewhere inbetween? Check the log file to see where exactly execution is halted with that generic error.

If you get the error at the very end, you can check intermediate results by successively placing an Exit Script; statement after each SELECT. Then reload and open the Table Viewer (Ctrl-T). Check whether fields and tables have names, spelling and residence that you expect.

As Miguel already meantioned, QlikView is case sensitive for table and field names, and some SQL databases have the unfortunate habit of converting field names to upper case without telling you.

Re: Expressions in Load

Anonymous — Tue, 22 May 2018 17:08:11 GMT

Peter - Many thanks! I used the Exit Script; statement to check after each load and found that the script was failing on the following statement:

STORE dispatchAnalysis INTO (qvd)

;

I don't understand why it was failing to execute the STORE command but I went to the folder, deleted the QVD file and re-ran the script and it worked fine.

Thanks for the tip!

-Nate