topic Re: Changing field values in Script in QlikView

Changing field values in Script

Mon, 28 Sep 2015 10:16:26 GMT

Hi,

I am wondering how to change the values of data in a field, through the script editor.

I import data from a spreadsheet currently and one of the fields is 'Group RM' and has lets say 10 values in it:

However I want to reduce to 5 overall, so i am left with 1,2,3,4 and OLD. Can I do this?

Thanks,

Luke

Re: Changing field values in Script

sunny_talwar — Mon, 28 Sep 2015 10:19:30 GMT

You can do something like this:

LOAD [Group RM],

If(Match([Group RM], 1, 2, 3, 4), [Group RM], 'Old') as [New Group RM]

From Source;

Re: Changing field values in Script

Mon, 28 Sep 2015 10:50:32 GMT

GRM
Carpenter
Dixon
Fok Chow
Gaines
Grigg
Hibbs
Hucker
Ingledew
King
Mostert

Hi Sunny,

I can't quite get my head round that. I can't find how to attach a file so have inserted a table for context, basically of the list of names I would like to combine Fok Chow, Group, GROUP MISC, Hibbs (Group), Ingledew, King, Lawlor and Mostert together and call it 'Sundry'. I currrently do this in Excel but really need to know how to do it within QV.

Thankyou,

Luke

Re: Changing field values in Script

sunny_talwar — Mon, 28 Sep 2015 10:55:04 GMT

While loading the data, just edit your GRM field itself or create a New GRM field)

If(Match(GRM, 'Fok Chow', 'Hibbs', 'Ingledew', ....), 'Sundry', GRM) as [New GRM]

If you have variations of name then you might be able to worksomething out with WildMatch

If(WildMatch(GRM, '*Fok Chow*', '*Hibbs*', '*Ingledew*', ....), 'Sundry', GRM) as [New GRM]

Re: Changing field values in Script

sasiparupudi1 — Mon, 28 Sep 2015 11:00:22 GMT

use a mapping load

map:

mapping load * inline

[

Value,Group

Fok Chow,Group

Hibbs,roup

Ingledew,Sundry

King,Sundry

Lawlor,Sundry

Mostert,Sundry

]

final:

laod GRM,...

applymap('map',GRM,'NA') as group

resident yourtable;

hth

Sasi

Re: Changing field values in Script

Mon, 28 Sep 2015 11:38:08 GMT

Hi Sasi,

I can't get this to work unfortunately and I also don't know how to attach a file to this...

Thanks,

Luke

(Sorry, I am still very new to this)

Re: Changing field values in Script

sasiparupudi1 — Mon, 28 Sep 2015 11:50:04 GMT

Hi Luke

Please have a look at the following post

How to attach qvw while replying to post

hth

Sasi

Re: Changing field values in Script

Anonymous — Mon, 28 Sep 2015 12:02:46 GMT

Try like this

Lo:

LOAD * INLINE [

GRM

];

load GRM as GRM2

Resident Lo Where GRM <6

Re: Changing field values in Script

Tue, 29 Sep 2015 15:39:00 GMT

Hi,

I have attached the file I was using as a test.

In the list box are all the values that are pulled in for the GRM field, and on the right are the correct values. All values in the list box that are not one of the correct values, must be changed to Sundry (bottom of right list). I hope this makes sense.

Hopefully you guys can help through the file and thankyou for your time,

Luke

Re: Changing field values in Script

Tue, 29 Sep 2015 15:46:04 GMT

Hi Luke,

Applymap would be the way to go with this. I appreciate you are very new, so it is probably worth you have a read of this

Data Cleansing

As that will help your understanding on the function.

Hope that helps

Joe

Re: Changing field values in Script

Tue, 06 Sep 2016 06:49:03 GMT

Thanks, Joe.

Regards,

MSK