Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Re: Rename Field Value if it Contains a Number

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
Highlighted
Master III
Master III

Re: Rename Field Value if it Contains a Number

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

Highlighted
Contributor III
Contributor III

Re: Rename Field Value if it Contains a Number

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

Highlighted
Master III
Master III

Re: Rename Field Value if it Contains a Number

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

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

Highlighted
Contributor III
Contributor III

Re: Rename Field Value if it Contains a Number

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