topic Re: Rename Field Value if it Contains a Number in QlikView

Rename Field Value if it Contains a Number

ecrobles18 — Tue, 01 May 2018 19:10:43 GMT

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

Re: Rename Field Value if it Contains a Number

sasiparupudi1 — Tue, 01 May 2018 19:21:22 GMT

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

Re: Rename Field Value if it Contains a Number

ecrobles18 — Wed, 02 May 2018 16:57:50 GMT

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

Re: Rename Field Value if it Contains a Number

sasiparupudi1 — Wed, 02 May 2018 17:33:25 GMT

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

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

Re: Rename Field Value if it Contains a Number

ecrobles18 — Wed, 02 May 2018 20:41:49 GMT

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