Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor
Contributor

Reload a task on QVMC when our data has updated

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

Labels (1)
5 Replies
Specialist
Specialist

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).

To help community find solutions, please don't forget to mark as correct.
Contributor
Contributor

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;

Specialist
Specialist

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.

 

To help community find solutions, please don't forget to mark as correct.
Support
Support

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..

 

 

Daniele - Senior Technical Support Engineer at Qlik
If a post helps to resolve your issue, please accept it as a Solution.
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.