Fixing data inside a column

[alec1982]

Hi guys,

I have a column that shows data as follow:

Division

Brokerage Service Division (BSD)

Brokerage Services Division (BSD)

Brokerage Services Divsion (BSD)

Corporate

Corporate Financial Services

Corporate Headquarters

Gallagher Bassett (GB)

Gallagher Bassett(GB)

Gallagher Benefit Services (GBS)

International Brokerage

Not Available

Risk Placement Services

SMI Corporate International

Special Marketing & International (SMI)

Special Marketing International (SMI)

-

As you see the first three rows are the same but the person who entered them didn't do a good job so one of them has Services instead of service and one is missing I in Division.

Is there anyway I can fix this to be one row instead of 3 raws?

Like can I do an if statement to have one instead of the three?

Thanks for your help.

Alec,

[jvitantonio]

Hi, you should be mjore specific. Can't you go to the data source and change it there? Would you like to change is before loading your data or we can do it directly in your pivot? Where exactly are you showing this?

Thanks

[Anonymous]

Is there some kind of ID field connected to the Division?

You could do something like this IF(Right(Division,5)='(BSD)','Brokerage Service Division',Division)

I would put that in the script.

[m_woolf]

You can correct with a mapping table

[alec1982]

Hi,

Thank you for your reply,

I think the best place to do is while loading the field in QVW.

load

ID,

Division,

Amount

from XYZ

Is there anyway I can add an if statement in here to change the value of the division.

Thxs,

[Anonymous]

Yes, just add  IF(Right(Division,5)='(BSD)','Brokerage Service Division',Division) As Division

It should work in your script.

[m_woolf]

SystemMap:
mapping load * inline [

Bad,Good
Brokerage Service Division (BSD),Brokerage Services Division (BSD)
Brokerage Services Divsion (BSD),Brokerage Services Division (BSD)
Gallagher Bassett(GB),Gallagher Bassett (GB)
Special Marketing & International (SMI),Special Marketing International (SMI)
.
.
.

];

Map Division using SystemMap;

[alec1982]

Hi,

Thank you for your help.

If my script is like Office:

Load

ID,

Division,

Amount

from XYZ;

where should I add the above script and hpow can i call it from this load.

Thxs,

Alec,

[m_woolf · Accepted solution]

Place the mapping table script above the Load statment for your current script.

[alec1982]

Thxs so much!