Update field in table based on another field in the same table

[sjhussain]

Dear All,

Hope everyone is fine.

We are looking to update a field in the table based on another field in the same table.

For example we have the following table.

Mobile No., Status, Activity

4041234, ACTIVE, New Registeration

4041234, ACTIVE, Change Mobile No.

4041234, ACTIVE, Change SIM

4041234, ACTIVE, Change Ownership

4040012, INACTIVE, New Registration

4040015, ACTIVE, New Registration

What we want to do is add a new field by the name of [NewStatus] and the condition is as follows:

If there is any Activity by the name of "Change Mobile No." for any mobile number then all the fields of NewStatus would be CHANGEMOBILE else it will that NewStatus will be the same as Status.

The resultant should be as follows:

Mobile No., Status, Activity, NewStatus

4041234, ACTIVE, New Registeration, CHANGEMOBILE

4041234, ACTIVE, Change Mobile No., CHANGEMOBILE

4041234, ACTIVE, Change SIM, CHANGEMOBILE

4041234, ACTIVE, Change Ownership, ACTIVE

4040012, INACTIVE, New Registration, INACTIVE

4040015, ACTIVE, New Registration, ACTIVE

You can see from above that which ever the Mobile No. had an activity by the name of "Change Mobile No." then all the NewStatus values are CHANGEMOBILE else it remains the same as Status.

Will appreciate your assistance.

Thanks

[Anonymous]

then try like this at script level:

load

*,

if(Activity='Change Mobile No','CHANGEMOBILE') as NewStatus

from YourTable;

Hope this will help!!

[Anonymous]

slight modification:

load

*,

if(Activity='Change Mobile No','CHANGEMOBILE','YourRequiredField') as NewStatus

from YourTable;

where YourRequiredField may be Status or Activity, depends on your requirement

[tamilarasu]

Hi Syed,

Try,

Load *,
If( Activity='Change Mobile No.' or Previous([Mobile No.])=[Mobile No.] ,'CHANGEMOBILE',Status) as NewStatus;

LOAD * INLINE [
    Mobile No.,  Status,  Activity
    4041234,  ACTIVE,  New Registeration
    4041234,  ACTIVE,  Change Mobile No.
    4041234,  ACTIVE,  Change SIM
    4041234,  ACTIVE,  Change Ownership
    4040012,  INACTIVE,  New Registration
    4040015,  ACTIVE,  New Registration
];

Output,

Note: Mobile number filed should be sorted before doing the preceding load.

[avinashelite]

Table_Name:

Load

*,

if(Activity='Change Mobile No','CHANGEMOBILE',Status) as NewStatus

from

Table_name;

[sjhussain]

Dear Tamil,

Thanks for the response. 

The activity Change Mobile No. can occur at any row.  For example New Registration, Change Sim, Change Mobile No.  In this case I cannot change the NewStatus field for the first row of the Mobile No. to CHANGEMOBILE.

[avinashelite]

could you please share the sample data and the desired output sample

[geert_gelade]

With this code, New Status is "CHANGEMOBILE" for all rows

Data:
LOAD * INLINE [

    "Mobile No.", "Status", "Activity"
    "4041234", "ACTIVE", "New Registeration"
    "4041234", "ACTIVE", "Change Mobile No."
    "4041234", "ACTIVE", "Change SIM"
    "4041234", "ACTIVE", "Change Ownership"
    "4040012", "INACTIVE", "New Registration"
    "4040015", "ACTIVE", "New Registration"
];


Map: //get all numbers for which "Change Mobile No." occurs
mapping load [Mobile No.], 'CHANGEMOBILE'
resident Data
where Activity = 'Change Mobile No.';


Result:
load *, applymap('Map',[Mobile No.], Status) as NewStatus
resident Data;
drop table Data;

[tresesco]

I guess your expected output should have 'CHANGEMOBILE' for all four 4041234.

Mobile No., Status, Activity, NewStatus

4041234, ACTIVE, New Registeration, CHANGEMOBILE

4041234, ACTIVE, Change Mobile No., CHANGEMOBILE

4041234, ACTIVE, Change SIM, CHANGEMOBILE

4041234, ACTIVE, Change Ownership, CHANGEMOBILE

4040012, INACTIVE, New Registration, INACTIVE

4040015, ACTIVE, New Registration, ACTIVE

If so, try like:

Input:
Load * Inline [
"Mobile No.", Status, Activity
4041234, ACTIVE, New Registeration
4041234, ACTIVE, Change Mobile No.
4041234, ACTIVE, Change SIM
4041234, ACTIVE, Change Ownership
4040012, INACTIVE, New Registration
4040015, ACTIVE, New Registration
];

Map:
Mapping Load
      [Mobile No.], 'CHANGEMOBILE' as NewStatus
Resident Input where  Activity ='Change Mobile No.';

Output:
Load
      [Mobile No.], Status, Activity,
      ApplyMap('Map',[Mobile No.], Activity]) as NewStatus
Resident Input ;

Drop table Input;

Edit: Corrected applymap

[tamilarasu]

Then try with Mapping load.