Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
then try like this at script level:
load
*,
if(Activity='Change Mobile No','CHANGEMOBILE') as NewStatus
from YourTable;
Hope this will help!!
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
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.
Table_Name:
Load
*,
if(Activity='Change Mobile No','CHANGEMOBILE',Status) as NewStatus
from
Table_name;
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.
could you please share the sample data and the desired output sample
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;
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
Then try with Mapping load.