SQL Join not running on Qlikview Management Console (QMC)

[craig157]

Hi All,

I cannot get my dashboard to reload on QMC for some odd reason,  but it reloads and operates perfectly fine with no errors when I run using the qlikview application.

Could anyone advise as I've tried lots of different ways but still no joy!

Table1:

LOAD

    [ExtractDate] as FileMonthStartDate,

    [VolumeType] as[Volume Type],

    applymap('VolumeTypeMap',[VolumeType]) as VolumeId,

    [VolumeReadDate] as [Volume Read Date],

    [VolumeCount] as [Volume Count],

    [ExtractDate] as [Volume Extract Date],  

    [Id] as [Id],

    [AssetId] as [AssetId];   

SQL SELECT a.Id, av.ExtractDate, av.VolumeType, av.VolumeReadDate, av.VolumeCount, av.AssetId

from "Assets".dbo.Assets a

    left outer join AssetVolumes.dbo.AssetVolumes av on a.Id = av.AssetId

        order by a.Id, av.VolumeType, av.VolumeReadDate asc;

left Join (Table1)

Table2:

Load 

    [Id] as [AssetId],

    [SerialNumber] as [Serial Number],

    [AssetId] as [Asset Number],

    [Model] as [Model Name],

    [ChargebackCode] as [ChargebackCode];   

SQL SELECT * FROM "Assets".dbo.Assets;

Store Table1 into Data\S1-VAAVolumes.qvd (qvd);

DROP Table Table1;

The end of the error log looks like this:

2018-10-31 08:48:21 0317      [Id] as Id,

2018-10-31 08:48:21 0318 [AssetId] as [AssetId]

2018-10-31 08:48:21 Error:

2018-10-31 08:48:21 Execution Failed

2018-10-31 08:48:22 Execution finished.

And the actual error coming from QMC is:

(31/10/2018 08:48:23) Error: The task "Pulse V2/Aug 2018/Xeretec_Data_S1_S2_dev_v2_2018.qvw" failed. Exception: || QDSMain.Exceptions.TaskFailedException: Task execution failed with errors to follow. ---> QDSMain.Exceptions.ReloadFailedException: Reload failed ---> QDSMain.Exceptions.LogBucketErrorException: The Source Document was NOT reloaded successfully. DocumentPath=C:\ProgramData\QlikTech\Documents\Pulse V2\Aug 2018\Xeretec_Data_S1_S2_dev_v2_2018.qvw. || at QDSMain.AbstractReloadTask.VerifyConditions(IExecutingTaskResult executingTaskResult) || at QDSMain.AbstractReloadTask.Reload(String fileName, IExecutingTaskResult executingTaskResult, String sectionAccessUserName, String sectionAccessPassword, eReloadOptions reloadOption, String variableName, String variableValue, Boolean moniterCpuUsage) || --- End of inner exception stack trace --- || at QDSMain.AbstractReloadTask.Reload(String fileName, IExecutingTaskResult executingTaskResult, String sectionAccessUserName, String sectionAccessPassword, eReloadOptions reloadOption, String variableName, String variableValue, Boolean moniterCpuUsage) || at QDSMain.DistributeTask.PerformExecute(IExecutingTaskResult executingTaskResult) || --- End of inner exception stack trace --- || at QDSMain.DistributeTask.PerformExecute(IExecutingTaskResult executingTaskResult) || at QDSMain.Task.AbstractTask.TaskExecution(CurrentExecutionArgs args)

[andrey_krylov]

Hi Craig. Since it works fine with the application, the code is good. I am not an expert in log files, but perhaps the problem is with the sql connection. Win or sql authentication is used?

[Chip_Matejowsky]

The snippet from the Task log you provided indicates that the source document didn't reload correctly

(31/10/2018 08:48:23) Error: The task "Pulse V2/Aug 2018/Xeretec_Data_S1_S2_dev_v2_2018.qvw" failed. Exception: || QDSMain.Exceptions.TaskFailedException: Task execution failed with errors to follow. ---> QDSMain.Exceptions.ReloadFailedException: Reload failed ---> QDSMain.Exceptions.LogBucketErrorException: The Source Document was NOT reloaded successfully. 

You'll need to review the document log of the QVW associated with this failed task in order to get more information on why the reload of the QVW failed at the document level. If document logging is enabled, it will be stored in the same directory in which the QVW file is housed. If document logging is not enabled, you will need to open the QVW file in QlikView Developer, click Settings > Document Properties > General tab and ensure the Generate Logfile checkbox is ticked. Then run the reload task so that the document log is generated.

[craig157]

Hi Chip,

I already posted the end of the Log File where the error occurs, but as you can see it's not helpful at all.

The end of the error log looks like this:

2018-10-31 08:48:21 0317      [Id] as Id,

2018-10-31 08:48:21 0318 [AssetId] as [AssetId]

2018-10-31 08:48:21 Error:

2018-10-31 08:48:21 Execution Failed

2018-10-31 08:48:22 Execution finished.

[dplr-rn]

Craig

did you try explicitly naming the outputs in the select statement

SQL SELECT a.Id as Id,

           av.ExtractDate as ExtractDate,

....

etc.

[craig157]

Hi Dilip,

No I am doing that in the Load stage, but I gave it a go and it doesn't work.

Cheers though

[Chip_Matejowsky]

Ah. Yes, see that now. Thanks for the clarification. That is a pretty abrupt error with really no descriptive information.

Have you verified that the account running the QlikView Distribution Service (QDS) has all appropriate permissions on the DB? A test you can try is to login to a computer which has QV Desktop installed with the service account and attempt to reload the QVW.

Suggest you contact Qlik Support and get a case created if you need further assistance.

[craig157 · Accepted solution]

FINALLY Figured it out.

There was no problem with the syntax or the joins or the ODBC connection.

The User Permissions on the server I was using were only able to Read, not Write. So it was actually failing before this stage when trying to Store the Tables.

All fixed now!

Thank you dilipranjith‌, wmy‌ & @andreykrylov for your help.