Dynamic update statement

[#1]

Hello everybody.

In my loading script, I loaded, among the others, two fields (see them in the script below):

IncrementalLoadPreventivi:
LOAD ...,
    Num(0) AS "Status",
    ...,
   NUM([NUMERO PREVENTIVO]) AS "Numero preventivo",
    ...
FROM
...

The field 'Status' can be either 0 or 1 and is set by default to 0.

I defined the variable 'vNumeroPreventivo' that I can set in an Input table.

I want to be able to switch the value of Status, between 0 and 1, for the record where [NUMERO PREVENTIVO] = $(vNumeroPreventivo).

Therefore I created a button with a Dynamic update action. Te following is the statement I use:

UPDATE *
  SET Status = 1 - Status
  Where [Numero preventivo] = $(vNumeroPreventivo);

The statement doesn't work.

I have to change it like the following:

UPDATE *
  SET Status = 1
  Where [Numero preventivo] = $(vNumeroPreventivo);

or

UPDATE *
  SET Status = 0
  Where [Numero preventivo] = $(vNumeroPreventivo);

The last two statements work, but those don't allow me to automatically switch the value (every time I have to update the statement.

I need a statement that allows me to switch between 0 and 1.

I'm using a personal edition of QV11, so please don't post qvw files.

Regards,

Nicolò.

Messaggio modificato da Nicolò Antonietti Added the version of my QlikView.

[swuehl · Accepted solution]

You should be able to use a statement like

=If( only({<[Numero preventivo] = {$(vNumeroPreventivo)}>} Status),

'UPDATE *

  SET Status = 0

  Where [Numero preventivo] = $(vNumeroPreventivo);'

  ,

'UPDATE *

  SET Status = 1

  Where [Numero preventivo] = $(vNumeroPreventivo);'

  )

[#3]

Thank you, Swuehl, for your quick reply.

It's working perfectly fine.

Nicolò.