Load script- how to edit a record

[emilyrabbit]

HI all,

because the content lack , and I can't edit the resource.  So, I want to edit in the loading scrip, fulfill the [Sales Sector Region1] like below.

I write the below script and it goes wrong. Pls

RENAME TABLE DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN TO TEMP3;
DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN:
NoConcatenate
LOAD *,
if([Sales Sector Code]='2CN100','2CN100',[Sales Sector Region1])as [Sales Sector Region1],
if([Sales Sector Code]='2CN100','2CN100',[Sales Sector Region2])as [Sales Sector Region2]
Resident TEMP3;
Drop Table TEMP3;

[PrashantSangle]

[Sales Sector Region1] & [Sales Sector Region2] do you have this field in table?

[emilyrabbit]

HI ,Yes, [Sales Sector Region1] & [Sales Sector Region2] already exist in the table.

[PrashantSangle]

ok. then your script looks ok to me. might be have some space after value in your field so Try below

RENAME TABLE DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN TO TEMP3;
DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN:
NoConcatenate
LOAD *,
if(wildmatch([Sales Sector Code],'*2CN100*'),'2CN100',[Sales Sector Region1])as [Sales Sector Region1],
if(wildmatch([Sales Sector Code],'*2CN100*'),'2CN100',[Sales Sector Region2])as [Sales Sector Region2]
Resident TEMP3;
Drop Table TEMP3;

Regards,

Prashant Sangle

[emilyrabbit]

Hi , the system error is below:

[PrashantSangle]

I copied your code only. because of * it is including all value from earlier table too. So rename this new field 

if(wildmatch([Sales Sector Code],'*2CN100*'),'2CN100',[Sales Sector Region1])as [New Sales Sector Region1],
if(wildmatch([Sales Sector Code],'*2CN100*'),'2CN100',[Sales Sector Region2])as [New Sales Sector Region2]

[emilyrabbit]

Hi ,because in the front page ,I need to field name [Sales Sector Region1] ; so I rename rename it . And it shows error. Could you pls help checking it ?

RENAME TABLE DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN TO TEMP3;
DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN_1:
NoConcatenate
LOAD *,
if(wildmatch([Sales Sector Code],'*2CN100*'),'2CN100',[Sales Sector Region1])as [New Sales Sector Region1],
if(wildmatch([Sales Sector Code],'*2CN100*'),'2CN100',[Sales Sector Region2])as [New Sales Sector Region2]
Resident TEMP3;
Drop Table TEMP3;

RENAME TABLE DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN_1 TO TEMP4;
DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN:
NoConcatenate
LOAD *,
[New Sales Sector Region1] as [Sales Sector Region1],
[New Sales Sector Region2] as [Sales Sector Region2]
Resident TEMP4;
Drop Table TEMP4;

[PrashantSangle]

So in that case instead of using * after Load statement, write down all field name which are there in TEMP4.

[Kushal_Chawda]

@emilyrabbit  you need to drop field first before renaming it as it is already present in same load

drop fields [Sales Sector Region1],[Sales Sector Region 2];

DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN:
NoConcatenate
LOAD *,
[New Sales Sector Region1] as [Sales Sector Region1],
[New Sales Sector Region2] as [Sales Sector Region2]
Resident TEMP4;
Drop Table TEMP4;

[Kushal_Chawda · Accepted solution]

@emilyrabbit  or better do below which can save one load statement

RENAME TABLE DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN TO TEMP3;
DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN_1:
NoConcatenate
LOAD *,
if(wildmatch([Sales Sector Code],'*2CN100*'),'2CN100',[Sales Sector Region1])as [New Sales Sector Region1],
if(wildmatch([Sales Sector Code],'*2CN100*'),'2CN100',[Sales Sector Region2])as [New Sales Sector Region2]
Resident TEMP3;
Drop Table TEMP3;

RENAME TABLE DIM_SALES_SECTOR_FLUID_CONNECTORS_HAN_1 TO TEMP4;

drop fields [Sales Sector Region1],[Sales Sector Region2];

rename field [New Sales Sector Region1] to [Sales Sector Region1];

rename field [New Sales Sector Region2] to [Sales Sector Region2];