Reload a task on QVMC when our data has updated

[cooper_yonk]

Good morning

Weve got a number of tasks set up in QVMC to reload extracts and apps.  The data for these extracts come from a database that updates around 3am every morning.  At the moment we have set up the extracts to refresh around 5am to give time for any issues in the source data completing.

Is there a way to have QVMC start the extracts as soon as the source database has completed its update ie if a file is dropped into a folder and when that file is detected QVMC starts its reload.

I believe this is possible however I cant see anythign that clearly helps with my problem.  Is there anyone who can help me with this?

Thanks

Jonathan

[Claudiu_Anghelescu]

Hello,

You have to create a new Check.qvw and obviously a new task.

You need a LAST_UPDATE column from the database which indicate you the last update time of the database.

Import the new field in the Control.qvw and put the condition to run based on this field:

let vLastUpdateNew =  FieldValue('LAST_UPDATE',1);

IF LAST_UPDATE = Text(Date(Date#(Today()) and $(vLastUpdateNew)<>$(vLastUpdateOld), 'YYYYMMDD')) THEN

EXECUTE cmd.exe /C C:\QMSEDX.exe -task=YourTaskToReload

ELSE

EXIT SCRIPT
ENDIF

store LAST_UPDATE into ..\FOLDER\LAST_UPDATE.QVD(qvd);
DROP TABLES DW_SCHEDULE;

You have to create a variable to store the new value of LAST_UPDATE - vLastUpdateNew

and a variable to store the old value of LAST_UPDATE - vLastUpdateOld in order to stop the script after it run successfully for once time.

Your current task trigger should be changed to "On external event" (on EDX).

[cooper_yonk]

Hi Claudiu

Thanks for the reply.  This is my script but its not working, it runs but doesnt do anything other than load a field called "time" (which contains todays date, into my dashboard...what have I done wrong?

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

// Load Global settings
$(Include=..\Script\Global Settings.qvs);

SET vRefreshPath= '\\prod-qv-live\qlikview\test\jc\';

let vLastUpdateNew = FieldValue('time',1);

LOAD time
FROM
[Q:\Test\JC\timestamp_file.xlsx]
(ooxml, embedded labels, table is Sheet1);

IF time = Text(Date(Date#(Today()) and $(vLastUpdateNew)<>$(vLastUpdateOld), 'YYYYMMDD')) THEN

EXECUTE cmd.exe /C C:\QMSEDX.exe -task=nprinting_reload

ELSE

EXIT SCRIPT
ENDIF

store LAST_UPDATE into \\prod-qv-live\qlikview\test\jc\LAST_UPDATE.QVD(qvd);
DROP TABLES DW_SCHEDULE;

[Claudiu_Anghelescu]

First time check if the command it's executed with success:

Replace this condition:

IF time = Text(Date(Date#(Today()) and $(vLastUpdateNew)<>$(vLastUpdateOld), 'YYYYMMDD')) THEN

with this:

IF 1=1 THEN

If your task is not reloaded neither now you have to check the file Settings.ini located to:

C:\Windows\System32\config\systemprofile\AppData\Roaming\QlikTech\QlikViewBatch

where should be:

AllowExecute Command=1

Also the trigger for the task nprinting_reload should be "on EDX"

https://community.qlik.com/thread/291967

If everything will run with the condition 1=1 you have to modify the running condition and the variables because the work is not complete as is now.

[Daniele_Purrone]

You can setup an "External Event Trigger", and when the Database is updated launch an API call that executes the task.
You can do that with the PowerShell, for instance.
Or, many of our customers use this tool..

[Brett_Bleess]

If you are running 12.30/November 2018 or later, you have access to a new File Exists trigger option via the Publisher Support Tasks area:

https://help.qlik.com/en-US/qlikview/November2018/Subsystems/QMC/Content/QV_QMC/QMC_System_Supportin...

This way, you can have the DB write some file to a specific location this task is checking, once it sees the file appear, that can trigger your QlikView tasks, just be sure you create another task that deletes the file after the task runs, otherwise things will just keep firing etc.  Hopefully this may work for you, should be easiest solution in this case I believe.

If you are on older releases, you would have to use the Event Driven Execution (EDX) trigger in that case, and that is a bit more tricky...

Regards,
Brett