topic Re: Update multiple records using marco in QlikView

Update multiple records using marco

Wed, 10 Feb 2016 09:21:47 GMT

Dear Community,

I have a case in which I need to update existing records based on a filter object.

Basically, the case can be summed up as follows:

Update multiple rows (a certain loop is required)
The Update is filter based, only selected values in a filter object should be updated
The macro should be able to count the number of selected value in the filter object

The following pseudo-code represents what I aim for:

For int i=0 to GetSelectedCount(Filter)

Update Field where Field.value = Filter.Value

Next i;

Any help is much appreciated,

Sincerely,

Rawad

Re: Update multiple records using marco

petter — Wed, 10 Feb 2016 12:12:48 GMT

The short answer: QlikView does not have any easy to use method to do that safely.

The long answer:

Could you state how the use of this functionality should be?

Is it for many users to do ad-hoc?

Is it in QlikView Desktop for single user that can perform his own reload?

You could get write-back functionality in this way:

1) Buy off-the-shelf add-on, check market.qlik.com

2) Create you own - which can be a significant effort depending on your requirements

- web-service write-back - with frequent reloads or partial reloads

- file + database write-back - with frequent reloads or partial reloads

- use the still existing Dynamic Update functionality, poorly documented and not very robust:

This could look like your proposed method - but multi-user access to in-memory data model

will create huge problems. If used in a single user desktop environment it might work.

Re: Update multiple records using marco

Thu, 11 Feb 2016 08:19:27 GMT

Thanks for your reply Petter.

Let us suppose we are working in a single user desktop environment and consider the following method (which I tried):

Create a button and add to it a dynamic update event
Create a filter object and name it TestTest
The statement in the button is as follows: UPDATE Test SET Discount = '1123' WHERE TestTest='$(=only( [TestTest] ))'
Obviously, we need to update the record discount in the table Test

The problem with this method is that only one record can be updated. Suppose multiple values are to be selected, the method fails (no records are updated). Why do you think is that ? Is there any way to fix it?

Sincerely,

Rawad

Re: Update multiple records using marco

petter — Thu, 11 Feb 2016 10:04:13 GMT

From what I experienced earlier when working with Dynamic Update the UPDATE statement was perfectly capable of updating multiple values.

So the problem should be in how the UPDATE statement is constructed I would think. Unless there is some kind of bug...

Re: Update multiple records using marco

Thu, 11 Feb 2016 10:48:17 GMT

There doesn't seem to be any syntax errors. When only 1 value is selected in the filter object, the update statement works fine. Only when multiple values are selected, does the problem occur.

Any ideas as to why ? Please find attached the application I am doing the testing process with.

Looking forward to any input you may offer.

Re: Update multiple records using marco

petter — Thu, 11 Feb 2016 11:49:45 GMT

Answering quickly without having had a look at your app I am pretty sure that the problem is due to the WHERE part of:

UPDATE Test

SET Discount = '1123'

WHERE

TestTest='$(=only( [TestTest] ))'

TestTest will test for only one selected value ... which also the Only()-function insist on...

Having several selected values will return Null and thus WHERE will not match any values and nothing will be updated.