Update multiple records using marco

[#1]

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

[petter]

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.

[#3]

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

[petter]

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

[#5]

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.

[petter]

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.