topic Re: Search string for certain word (Calculated Dimension) in QlikView

Search string for certain word (Calculated Dimension)

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I want to create a calculated dimension which searches in a string for a certain word, and then if the string contains that word, is should be named something

Example

Dimension name : Field1

Field 1 contains below data strings

- shoe_OLD_4512

- Shirt45OLD_78942

- ShoeNEW_5698

- Shoe2OLD_56821

......

I want a calculated dimension that searches every string for the word Old, if the string contains the word OLD, then its should be named Old, otherwise New.

However, I can only seem to find a answer which searches the strings based on a locked position of the word your a searching for. Meaning that the word OLD should be in the same place every time for the calculated dimension to be working.

Is there a way to achieve it?

Any helped is very much appreciated.

Best

Stefan

Re: Search string for certain word (Calculated Dimension)

richard_chilvers — Wed, 17 May 2017 10:08:27 GMT

How about using INDEX?

IF(INDEX(Field1,'OLD')>0,'Old', 'New')

Re: Search string for certain word (Calculated Dimension)

devarasu07 — Wed, 17 May 2017 10:11:55 GMT

Hi,

Try like this

load *

,Replace(Dim,'OLD','New') as Cal

;

LOAD * INLINE [

Dim

shoe_OLD_4512

Shirt45OLD_78942

ShoeNEW_5698

Shoe2OLD_56821

];

Re: Search string for certain word (Calculated Dimension)

Sergey_Shuklin — Wed, 17 May 2017 10:18:07 GMT

Hello, Stefan!

You can use more intuitive expression: if(Filed1 like *old*,'OLD','NEW').

The one of advantages is it's not a case sensitive. So it's equaly will search for "OLD", "Old", "old" etc.

Re: Search string for certain word (Calculated Dimension)

antoniotiman — Wed, 17 May 2017 10:34:09 GMT

Hi Stefan

Calculated Dimension

=If(WildMatch(Field1,'*OLD*'),'Old','New')

Regards,

Antonio

Re: Search string for certain word (Calculated Dimension)

Anonymous — Wed, 17 May 2017 10:37:20 GMT

Hi Antonio

That worked! How easy can it be

Thanks to you all for responding to quick

Regards

Stefan