Changing field values in Script

[#1]

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:

1

2

3

4

5

6

7

8

9

10

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

[sunny_talwar]

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;

[#3]

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

[sunny_talwar]

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]

[sasiparupudi1]

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

[#6]

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)

[sasiparupudi1]

Hi Luke

Please have a look at the following post

How to attach qvw while replying to post

hth

Sasi

[Anonymous]

Hi

Try like this

Lo:

LOAD * INLINE [

    GRM

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

];

load GRM as GRM2

Resident Lo Where GRM  <6

[#9]

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

[#10]

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