Force fail of Qlik loading if no new column exists

[BSCIknowlec]

Hi experts,

looking for some help

i have a large app that is constantly loading and sometimes its loading dispute them having no new data and therefore taking up a lot of server resources.

so what i want to do is create a small new app that holds a certain column and will only successfully reload when a new column entry is added, then ill have my large app dependent on this app, i.e. it only reloads on success of new small app loads.

the main issue im having is how best to do this, in the sql select statement or in the qlikview script?

the column i want to "peek" is a simple timedate column, so i will do an initially first time only load that pulls in all the fields, then every 5 mins it will load this app and if there is a new column it will load successfully, or fail if not. 

i hope to get some advice on how do to this as im sure its a simple fix?

any help would be appreciated.

thanks

[marcus_sommer]

New column sounds that there are crosstable loaded/created - for many views/handlings it's not the best data-structure so a transforming with The-Crosstable-Load might be more suitable.

Nevertheless if it's really a new column you could use: nooffields('YourTable') to count them. Otherwise by a check of new field-values you takes: fieldvaluecount('YourField'). Of course you need to store these information in each iteration within a variable to be able to compare the previous value against the new one.

After that I wouldn't tend to force an error because you would create each time an error-message from the qmc that a task has failed. Although you could just ignore this message and/or moving/deleting it with some rules within your mail-client.

I think I would use some kind of loop in which QlikView waits n minutes if no new columns/records are available and repeat it n times maybe something (simplified) like this:

load datetime from source;

for i = 1 to 10
   if fieldvaluecount('datetime') > $(var) then
      let var = fieldvaluecount('datetime');
      load ….
      /* update */ exit for;
   else
      sleep 300000;
   end if
next

- Marcus

[BSCIknowlec]

Hi Marcus,

thanks for getting back to me. 

sorry for the confusion, it is one column but its the field within that column that i want to monitor. 

my apologies but i dont quite follow. is my variable just to store the newest value in my 'datetime' column?

then i run a loop to  only pull the most recent data value in my datetime column and if it matches my variable, the script doesn't load(or pauses as you suggested) but if it is different, the script loads and then this new value becomes the new var?

is there a way to do that in my load statement? 

thanks

[marcus_sommer]

My suggestion was the simplest one - just counting the field-values. In regard to new values it should be enough. Nevertheless you may change it to pull the max. value into the variable - if you used any incremental logics whereby it mustn't be mandatory because the check for new values should still work.

Further I added a pre-load on top of the loop on which the check happend. Of course it means a certain overhead but you won't manage to get an extended logic without some of it - and I doubt that the preload takes to much resources.

If you put your entire load on the place of:   

load ….

from my example above it should work. Maybe you don't do it directly with your load else you makes a simple dummy with some autogenerated records and lesser sleep and loop-iterations just to comprehend the logic.

Beside this it may quite useful if you split your load into multiple logically parts using a multi-tier data-architecture because it keeps the loads simple and readable/maintainable and the various parts could run in parallel and/or different time-frames.

- Marcus

[BSCIknowlec]

Apologies marcus,

im a bit unsure as to why you put "for i = 1 to 10" at the start? what is this for? 

Also, i tried creating a variable with the fieldvaluecount function in both the script and front end but i get back null values for my selected field "datetime". ive attached the values in my field if that helps? 

thanks

[marcus_sommer · Accepted solution]

The outer for-loop is to repeat the loading-attempt n times and to get an easy possibility to exit the entire execution to not to produce a never ending loop. Alternatives could be to use recursive called sub-routines - but it wouldn't be easier.

In your case it could mean 10 * 5 minutes + a bit overhead of the check and for the case the load happens in the last iteration the whole would be probably finished shortly less of an hour. If now your task is set to hourly it shouldn't conflict. In the end it means you could play with the number of iterations and the sleeping-times and your task-settings ... until it fulfilled the requirements.

That your variable showed no value means that they isn't filled properly - means any syntax-issue and/or misspelling of the field/variable-names or similar.

My suggestion from above needs further some adjustments because the pre-load for the check is on the outside from the loop and needs also be dropped each time - therefore change it to:

/* update */  let var = rangemax($(var), 0);

for i = 1 to 10
    /* update */ temp: load datetime from source;
   if fieldvaluecount('datetime') > $(var) then
      let var = fieldvaluecount('datetime');
       /* update */ drop tables temp;
      load ….
      /* update */ exit for;
   else
      sleep 300000;
       /* update */ drop tables temp;
   end if
next

- Marcus