Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
];
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.
Will it always be just two words you are looking for or will there be a variable number of words? If so how many?
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.