How do I rename field values?

[Anonymous]

I have a field called 'group' and it has 6 possible values - '1. Under 10hr', '2. Under 40hr', '3. under 80h', '4. Under 160', '5. Under 320', '6. Over 320'.   

The possible values aren't very clear.  I would like to present these differently in all my charts and tables.  I would like to rename them to - 'Under 10 hours', '10 up to 40 hours', '40 up to 80 hours', '80 up to 160 hours', '160 up to 320 hours', '320 hours up to FT'.

Is there a way to do this in the 'edit script'.?

Thanks

Greg

[krishna20]

Hi

Edit this code below to group field .

Try like this

if(Group<0 ,Dual('<0',1),

if(Group>1 and Group<=10,Dual('1-10',2),

if(Group>=11 and Group<=40,Dual('11-40',3),

if(Group>=41 and Group<=80,Dual('41-80',4),

if(Group>=81 and Group<=160,Dual('41-160',5),

)))) as  Hours_band

Hope this helps !!

Regards

Krishna

[daveamz · Accepted solution]

Hello Greg,

This should work:

Pick(Match(group, '1. Under 10hr', '2. Under 40hr', '3. under 80h', '4. Under 160', '5. Under 320', '6. Over 320'), 'Under 10 hours', '10 up to 40 hours', '40 up to 80 hours', '80 up to 160 hours', '160 up to 320 hours', '320 hours up to FT')

It is very important to list the elements in the same order because first match will return first pick item and so on.

Or, you can use Mapping Load / Apply Map.

Regards,

David

[MarcoWedel]

=Dual(Pick(Match(group, '1. Under 10hr', '2. Under 40hr', '3. under 80h', '4. Under 160', '5. Under 320', '6. Over 320'),

                 'Under 10 hours', '10 up to 40 hours', '40 up to 80 hours',

                 '80 up to 160 hours', '160 up to 320 hours', '320 hours up to FT'),

          Match(group, '1. Under 10hr', '2. Under 40hr', '3. under 80h', '4. Under 160', '5. Under 320', '6. Over 320'))

[#5]

Hi Greg,

I answered something similar the other day, maybe have a look at this

http://community.qlik.com/thread/148849

hope that helps

Joe

[MarcoWedel]

Dual to let this field be sortable numerically.

[rwunderlich]

I would use a MAPPING load.

GroupMap:

MAPPING LOAD from, dual(to,RecNo()) INLINE [

from, to

1. Under 10hr, Under 10 Hours

2. Under 40hr, 10 to 40 Hours

etc

]

;

MAP Group USING GroupMap;

The dual() trick will set up a numeric value for sorting. Just list the rows in order in the mapping table.

-Rob

http://masterssummit.com

http://robwunderlich.com

[Anonymous]

Krishna, Marco, David, Joe, Rob

Thank you for your assistance.

Greg

[AshwinDaniel]

Hello Gentlemen,

Is there any way to display Alias name for field values using expression. 

I do have only two field values under a field "Eligible Criteria" (For example).

The first one has "Fuel Vehicle Eligible" which I would like to show as "Eligible" and next one "Not eligible due to"  as "Not Eligible"

Thanks in advance!

@Anonymous @rwunderlich @MarcoWedel @daveamz @krishna20

[rwunderlich]

If("Eligible Criteria" = 'Fuel Vehicle Eligible', 'Eligible', 'Not Eligible')