Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
ecrobles18
Contributor III
Contributor III

Rename Field Value if it Contains a Number

Hello All,

     I am working on an enhancement to an existing report. In this information that comes from a third party service company there is a field [ShipmentExtractRecord.PickLocation/Location/Name] that contains many different values. I have been asked to label the type of location by grouping what is in the contained in the name. The field [ShipmentExtractRecord.PickLocation/Location/Name] contains multiple name values such as,

 

GILLILAN 1-35-26XH,BLAINE
GOLDEN 1306 1 WH18,KINGFISHER
GRINDSTONE 2 FEE 2H,CULBERSON
HALWORLD - MONA
HARDESTY 1H2215,GRADY
HARDESTY 1H-22-15,GRADY
HES MULTICHEM  CANADIAN
HES MULTICHEM - CANADIAN
HEXION STEEL CITY
HICKMAN 2107 1MH22,GARFIELD
HICKMAN 2107 1MH-22,GARFIELD

The request is for anything that contains Halworld to be labeled "Halworld", anything containing a number, HARDESTY 1H2215,GRADY, in the name would be labeled "Diversion", and Lastly anything else, HEXION STEEL CITY, would be labeled "Other". The intent is to have this grouped and dispalyed on a chart. I am looking for insight on what the best approach would be here. If I write this in an if statement I would like to know how to account for the names that contain a number, as you see there is no standard on where the number is placed in the name. Kindly, can any lead some help on this topic? Your help would be much appreciated.

-Emmanuel Robles

1 Solution

Accepted Solutions
ecrobles18
Contributor III
Contributor III
Author

Sasidhar,

     Again thank you for the insight this definitely help. However, I did have to modify a bit to get the output I was looking for.

If(SubstringCount([ShipmentExtractRecord.PickLocation/Location/Name],'HALWORLD')>0, 'HALWORLD',

If(IsNum(KeepChar([ShipmentExtractRecord.PickLocation/Location/Name],'0123456789')), 'DIVERSION','OTHER')) As NEWFIELD

Your help is much appreciated.

-Emmanuel

View solution in original post

4 Replies
sasiparupudi1
Master III
Master III

May be like this?

If(SubstringCount(YourField,'HALWORLD')>0, YourField) As Halworld,

If(IsNum(KeepChar(YourField,'0123456789')), YourField) As Diversion,

f(SubstringCount(YourField,'HALWORLD')=0 AND IsNum(KeepChar(YourField,'0123456789'))=0, YourField) As Other

ecrobles18
Contributor III
Contributor III
Author

Sasidhar,

Thank you for this but I need to assign the new name and keep them under the same field. This logic doesn't seem to work when I apply the same functions to a single field. Could you suggest how to solve it in that scenario? Thanks again for your help.

-Emmanuel

sasiparupudi1
Master III
Master III

If(SubstringCount(YourField,'HALWORLD')>0, YourField) ,'Halworld',

If(IsNum(KeepChar(YourField,'0123456789')), YourField) , 'Diversion','Other' ) ) As NewField

ecrobles18
Contributor III
Contributor III
Author

Sasidhar,

     Again thank you for the insight this definitely help. However, I did have to modify a bit to get the output I was looking for.

If(SubstringCount([ShipmentExtractRecord.PickLocation/Location/Name],'HALWORLD')>0, 'HALWORLD',

If(IsNum(KeepChar([ShipmentExtractRecord.PickLocation/Location/Name],'0123456789')), 'DIVERSION','OTHER')) As NEWFIELD

Your help is much appreciated.

-Emmanuel