Parsing in Qlikview

[newqlik2017]

Hello,

I have a column with below values:

New Haven County
Fairfield County
Los Angeles County
Atlantic County

Result should be:

New Haven
Fairfield
Los Angeles
Atlantic

Result am getting by using IF(CNTY_NM LIKE '*COUNTY*',  SUBFIELD(CNTY_NM, ' ', 1))
New

Fairfield

Los
Atlantic

Thanks.

[m_woolf]

if(right(CNTY_NM,,6)='County',left(CNTY_NM,len(CNTY_NM)-7),CNTY_NM)

[MarcoWedel]

IF(CNTY_NM LIKE '*COUNTY*',  SUBFIELD(CNTY_NM, ' County', 1))

[newqlik2017]

@Marco Wedel - The county word still appears after the county name, which I need to get rid of.

@m w - I got error in the expression but I tweaked your version to this and it seems to be working

LEFT(CNTY_NM, len(CNTY_NM)-7)

Thank you both.

[juraj_misina]

How about simple Replace(CNTY_NM, ' County', '')?

[m_woolf]

Slick Marco. I would mark yours  or Juraj's as Correct, but they will all work.

[MarcoWedel · Accepted solution]

seems to work for me

LOAD *,
    IF(CNTY_NM LIKE '*COUNTY*',  SUBFIELD(CNTY_NM, ' County', 1)) as Result
INLINE [
    CNTY_NM
    New Haven County
    Fairfield County
    Los Angeles County
    Atlantic County
];

[newqlik2017]

@Marco Wedel - your solution works perfect. I had County as uppercase instead of lowercase on my script so I overlooked.

@ Juraj Misina - your solution works as well.

Thank you all.

[Anonymous]

You can also simplify and use Replace(CNTY_NM , ' County', '').

[MarcoWedel]

What about these counties: