Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

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

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

9 Replies
Champion
Champion

then try like this at script level:

load

*,

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

from YourTable;

Hope this will help!!

Champion
Champion

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,

Capture.PNG

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;

Partner
Partner

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

Creator
Creator

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;

MVP
MVP

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.