topic Overwrite a field with a calculated filed within the same script in QlikView

Overwrite a field with a calculated filed within the same script

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

HI, This works but i'm looking to condense it to one Load if possible to reduce script run time and memory/CPU usage usage - tables are millions of rows in size. I need to clean out everything in the Agent field past the ":", but the field name must remain as Agent in the final data.

DATASET_2:

Load *

,left(Agent,index(Agent,':')-1) as Agent2

Resident DATASET;

DROP table DATASET;

Drop field Agent;

DATASET_3:

,Agent2 as Agent

Resident DATASET_2;

DROP table DATASET_2;

Thanks,
Dave

Re: Overwrite a field with a calculated filed within the same script

techvarun — Mon, 13 Nov 2017 05:18:17 GMT

Try the below code in your first load itself like below

DATASET:

LOAD left(Agent,index(Agent,':')-1) as Agent FROM ......

You can eliminate all the other steps Dateset2,dataset3, Resident, Drop field etc

Thanks,

Varun

Re: Overwrite a field with a calculated filed within the same script

Anil_Babu_Samineni — Mon, 13 Nov 2017 05:18:37 GMT

You can't do like this? This makes better where you expected

DATASET:

Load @1, @2, Agent, left(Agent,index(Agent,':')-1) as Agent2;

DATASET:

Load @1, @2, Agent, left(Agent,index(Agent,':')-1) as Agent2;

Drop Field Agent From DATASET;

RENAME Field Agent2 to Agent;

Re: Overwrite a field with a calculated filed within the same script

techvarun — Mon, 13 Nov 2017 05:23:27 GMT

Or You can try

DATASET:

LOAD SubField(Agent,':',1) as Agent FROM ......

Re: Overwrite a field with a calculated filed within the same script

Anonymous — Mon, 13 Nov 2017 05:44:17 GMT

Thanks both, Rename Field was the trick I was looking for.

Varun, I couldn't do it that way because I was also loading all fields "*" so it was causing a column name conflict error. I'll try the subfield formula though, thanks for that.