Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
;
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.
hello
could you post the error log ?
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?
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
;
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
;
daSum:
LOAD
da.vehicleNum AS vehicleNum,
da.DispStartDate AS DispStartDate,
da.driver As driver,
Sum(da.DTInacurate) As InAccStopSum
FROM
GROUP BY
da.vehicleNum,
da.DispStartDate,
da.driver
;
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.
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
;
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