Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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;
Or You can try
DATASET:
LOAD SubField(Agent,':',1) as Agent FROM ......
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.