Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Changing field values in Script

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

10 Replies
Highlighted

Re: Changing field values in Script

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;

Highlighted
Not applicable

Re: Changing field values in Script

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

Highlighted

Re: Changing field values in Script

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]

Highlighted
Master III
Master III

Re: Changing field values in Script

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

Highlighted
Not applicable

Re: Changing field values in Script

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)

Highlighted
Master III
Master III

Re: Changing field values in Script

Hi Luke

Please have a look at the following post

How to attach qvw while replying to post

hth

Sasi

Highlighted
Specialist
Specialist

Re: Changing field values in Script

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

Highlighted
Not applicable

Re: Changing field values in Script

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

Highlighted
Not applicable

Re: Changing field values in Script

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