Overwrite a field with a calculated filed within the same script

[Anonymous]

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

[techvarun]

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

[Anil_Babu_Samineni · Accepted solution]

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

DATASET:

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

OR

DATASET:

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

Drop Field Agent From DATASET;

RENAME Field Agent2 to Agent;

[techvarun]

Or You can try

DATASET:

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

[Anonymous]

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.