topic Re: Update field in table based on another field in the same table in QlikView

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

sjhussain — Wed, 09 Dec 2015 05:35:22 GMT

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

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

Anonymous — Wed, 09 Dec 2015 05:43:37 GMT

then try like this at script level:

load

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

from YourTable;

Hope this will help!!

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

Anonymous — Wed, 09 Dec 2015 05:46:05 GMT

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

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

tamilarasu — Wed, 09 Dec 2015 05:49:32 GMT

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.

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

avinashelite — Wed, 09 Dec 2015 05:56:57 GMT

Table_Name:

Load

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

from

Table_name;

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

sjhussain — Wed, 09 Dec 2015 06:01:21 GMT

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.

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

avinashelite — Wed, 09 Dec 2015 06:05:20 GMT

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

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

geert_gelade — Wed, 09 Dec 2015 06:06:26 GMT

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;

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

tresB — Wed, 09 Dec 2015 06:07:02 GMT

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

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

tamilarasu — Wed, 09 Dec 2015 06:08:29 GMT

Then try with Mapping load.