topic Re: Fixing data inside a column in QlikView

Fixing data inside a column

alec1982 — Tue, 04 Sep 2012 14:01:33 GMT

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,

Re: Fixing data inside a column

jvitantonio — Tue, 04 Sep 2012 14:23:48 GMT

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

Re: Fixing data inside a column

Anonymous — Tue, 04 Sep 2012 14:25:11 GMT

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.

Re: Fixing data inside a column

m_woolf — Tue, 04 Sep 2012 14:34:18 GMT

You can correct with a mapping table

Re: Fixing data inside a column

alec1982 — Tue, 04 Sep 2012 14:45:12 GMT

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,

Re: Fixing data inside a column

Anonymous — Tue, 04 Sep 2012 14:48:00 GMT

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

It should work in your script.

Re: Fixing data inside a column

m_woolf — Tue, 04 Sep 2012 14:53:35 GMT

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;

Re: Fixing data inside a column

alec1982 — Tue, 04 Sep 2012 18:05:50 GMT

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,

Re: Fixing data inside a column

m_woolf — Tue, 04 Sep 2012 18:08:16 GMT

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

Re: Fixing data inside a column

alec1982 — Tue, 04 Sep 2012 18:52:56 GMT

Thxs so much!