Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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
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]
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
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)
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
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
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
As that will help your understanding on the function.
Hope that helps
Joe