topic Re: Find first repeated word in text in QlikView

Find first repeated word in a text

Mon, 15 Feb 2016 04:16:16 GMT

I have the table below in .csv format:

Records

something-Spiderman-something-something

something-Spiderman-something-X-Man

Spiderman-something-something-something

X-Man-something-Spiderman-something-something-something

X-Man-something-something-something-something

I want to form another column so the result would be like:

Records , TheHero

something-Spiderman-something-something , Spiderman

something-Spiderman-something-X-Man , Spiderman

Spiderman-something-something-something , Spiderman

X-Man-something-Spiderman-something-something-something , X-Man

X-Man-something-something-something-something, X-Man

The "something" value can be any text and it does not follow a format.

The ordering of attributes in the field might be different.

I need to find first occurrence of a word (either "Spiderman" or "X-Man") from the left.

Thank you.

Re: Find first repeated word in text

swuehl — Mon, 15 Feb 2016 16:49:13 GMT

Not really sure about your requirements, but you can look into Index() function to retrieve the position of a substring found in your text values.

Re: Find first repeated word in text

sunny_talwar — Mon, 15 Feb 2016 16:53:36 GMT

May be this:

Table:

LOAD Records,

If(Len(Trim(SubField(Replace(Replace(Records, 'something', '|'), '-', ''), '|', 1))) > 0,

SubField(Replace(Replace(Records, 'something', '|'), '-', ''), '|', 1),

SubField(Replace(Replace(Records, 'something', '|'), '-', ''), '|', 2)) as NewRecords;

LOAD * Inline [

Records

something-Hotman-something-something

something-Hotman-something-XMan

Hotman-something-something-something

XMan-something-Hotman-something-something-something

XMan-something-something-something-something

];

Re: Find first repeated word in a text

Mon, 15 Feb 2016 22:56:16 GMT

I add more information to the question:
The "something" can be any text and it does not follow a format, so I can't use replace function.

Re: Find first repeated word in a text

petter — Mon, 15 Feb 2016 23:11:47 GMT

Will it always be just two words you are looking for or will there be a variable number of words? If so how many?

Re: Find first repeated word in a text

swuehl — Mon, 15 Feb 2016 23:53:46 GMT

You can find the first of a variable number of heros like

MAP:
MAPPING LOAD 
 Hero, '\/' & Hero & '/\' as F2
INLINE [
Hero
Hotman
XMan
];

Table:
LOAD Records,
  TextBetween(MapSubString('MAP',Records), '\/','/\',1) as FirstHero,
  TextBetween(MapSubString('MAP',Records), '\/','/\',2) as SecondHero;
LOAD * Inline [
Records
something-Hotman-something-something
something-Hotman-something-XMan
Hotman-something-something-something
XMan-something-Hotman-something-something-something
XMan-something-something-something-something
];

I've used '\/' and '/\' as delimiters for the heros, just use any sequence of characters that won't show up in your "something" text parts.