topic Re: Create a new calculated field using two fields in a table in the script in QlikView

Create a new calculated field using two fields in a table in the script

mayuranp — Mon, 15 Jan 2018 17:16:45 GMT

I have simplified my query.

Let's say I have a table with columns A,B,C already loaded.

Now I want to create a new column 'D' in this table by adding B+C.

I know it can be done as below, but don't want to create another table.

NewTable:

LOAD *,

B+C as D

RESIDENT OldTable;

DROP TABLE OldTable;

Another question, if the above is the only method, is it the most efficient operation considering the table can have a million+ records.

Thanks

Re: Create a new calculated field using two fields in a table in the script

sunny_talwar — Mon, 15 Jan 2018 18:17:07 GMT

You don't have to do this in a resident load... you can do this in your first OldTable....

OldTable:

LOAD A,

B+C as D

FROM ....;

Re: Create a new calculated field using two fields in a table in the script

mayuranp — Mon, 15 Jan 2018 18:23:28 GMT

Yes, but my query is the table is already loaded and I cannot alter that.

In my case the table is already loaded from few qvd files using concatenation and joins. Now I want to add a new field.

Re: Create a new calculated field using two fields in a table in the script

sunny_talwar — Mon, 15 Jan 2018 19:57:21 GMT

what do you mean it is already loaded? You will reload everything again to add the new column, right? Are you not planning to reload your application on a fixed schedule?

Re: Create a new calculated field using two fields in a table in the script

rubenmarin — Mon, 15 Jan 2018 21:06:19 GMT

Hi Mayuran, you can rename the old table to keep to keep the original name in the new table:

Rename oldTableName to TableNameToDrop:

oldTableName:

LOAD ..

Resident TableNameToDrop;

DROP TableNameToDrop;

Re: Create a new calculated field using two fields in a table in the script

maxgro — Mon, 15 Jan 2018 21:34:15 GMT

if your table has a primary key (pk) you can use a left join

ie if A is your pk

left join (OldTable) load

B+C as D

resident OldTable;