topic Re: Re: how to update a field after load in QlikView

how to update a field after load

Thu, 02 Jan 2014 23:03:53 GMT

Hi all,

After loading a large table, I will need to select an individual record by "TRANS_NUMBER" and update "status" column with text. How do I do that? The transaction_number column values are alphanumeric. And is there a way to select multiple records by TRANS_NUMBER and updating them all with the same status?

For example:

TRANS_NUMBER	Status
1347	Needs Action

I would like to change the Status of TRANS_NUMBER 1347 from Needs Action to Approved.

Thanks in advance for your help!

Re: how to update a field after load

swuehl — Fri, 03 Jan 2014 01:22:27 GMT

You can use Dynamic Update to update your field values on the server, but these changes will not persist or promote to the source DB.

If that's what you want, enable Dynamic update, then create a button with External - Dynamic Update action and a statement like

UPDATE TransactionTable SET status='Approved' WHERE TRANS_NUMBER=1347;

Re: how to update a field after load

Fri, 03 Jan 2014 02:26:26 GMT

Create the Mapping table and create the new field or change the value on the existing field while loading the data into qlikview..

Re: how to update a field after load

Mon, 06 Jan 2014 21:36:35 GMT

Swuehl,

The TRANS_NUMBER wont always be 1347. The TRANS_NUMBER will depend on what I select and there may be multiple TRANS_NUMBER selected at once. Please advise.

Re: how to update a field after load

Mon, 06 Jan 2014 21:37:56 GMT

Dathu_qv,

I will need to load the script first and view the table before making changes. Does a mapping table still work for this?

Re: how to update a field after load

Tue, 07 Jan 2014 03:13:18 GMT

Absolutely it works...

Temp:

LOAD * FROM SourceTable ;

MAP_TAB:

Mapping LOAD KEY, VALUE From source ;

MAIN:

LOAD *, ApplyMap('MAP_TAB',KEYFIELD,KEYFIELD) AS NEWFILED

Resident Temp;

DROP Table Temp;

Please post some sample data if you need full script....

Re: Re: how to update a field after load

swuehl — Tue, 07 Jan 2014 09:35:15 GMT

Ok, just create variables for the set of Transaction numbers and the value to change to, then the statement could look like:

UPDATE Trans SET Value= vValue WHERE match(TransNumber,$(vTrans));

Re: Re: how to update a field after load

Tue, 07 Jan 2014 18:34:20 GMT

that did the job thanks swuehl!!!

Re: how to update a field after load

d_brown — Wed, 20 Aug 2014 09:38:55 GMT

I have tried your example app, I am using v11.2 SR5 and upgraded to SR7, but it doesn't appear to work in either. What version are you using?

Re: how to update a field after load

Wed, 20 Aug 2014 10:51:26 GMT

It's not working for me too, I am using SR7.

Re: how to update a field after load

Hemanth458 — Mon, 06 May 2019 11:18:37 GMT

where can i write this statement in qlik scripting part

UPDATE TransactionTable SET status='Approved' WHERE TRANS_NUMBER=1347;