Applying logic to certain rows only

[#1]

Hi I have a problem where the sales figures are out by a factor of 10 for a certain sales person in a certain region. 

So from my raw file I want to work out for rows where 'SALESPERSON' equals John and 'REGION' equals 'AMERICA' then  divide the 'SALES' for these lines by 10.

Would I do this in script and if so how would this write out ? Note I am not changing all the 'SALES' figures but just those particular ones.

[sinanozdemir]

Hi,

Without seeing the data, it is a bit difficult. Although, you can do something similar to the below:

LOAD

     If(SALESPERSON = 'John' And REGION = 'AMERICA', SALES/10) As [Sales Factor]

[#3]

Thanks but I dont want to create a separate field. I think the logic would need to be applied to the original 'SALES' field if im not mistaken

[kaushiknsolanki]

HI,

You can write the If statement in your script to do this. (Applicable only for few Salesperson).

Load *,if(Salesperson = 'John' and Region = 'America',Sales/10,Sales) as New_Sales

from XYZ;

If you have a big list with different factors then try below script.

Map:

Mapping Load Salesperson&'-'&Region as Key, Factor

From PQR;

Data:

Load *,Sales/if(isnull(Factor),0,Factor) as New_Sales;

Load *,Applymap('Map',Salesperson&'-'&Region) as Factor

From XYZ;

Regards,

Kaushik Solanki

[nagaiank]

If name of your table having sales data (your field SALES) is SalesTable, you may use the following script. Use UpdatedSALES instead of SALES in your objects.

Sales:

Load *,If(SALESPERSON='John' and REGION='AMERICA', SALES/10,SALES) as UpdatedSALES;

Load * From ..... SalesTable;

[sinanozdemir]

LOAD

     If(SALESPERSON = 'John' And REGION = 'AMERICA', SALES/10, SALES) As SALES

In this case, you are not creating extra field and handling everything within SALES field.

[sasiparupudi1 · Accepted solution]

If(lower(SALESPERSON) = 'john' And upper(REGION) = 'AMERICA', SALES/10,SALES) As SALES

[#8]

Perfect - that's what I was looking for.