topic Re: Removing multiple occurances of a substring in QlikView

Removing multiple occurances of a substring

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts ,

I have a scenario where I am getting multiple substring inside a string , I need to reduce the multiple occurrence to 1st occurance .

eg :

Input:

'this test is a test string test '

Output:

'this test is a string'

I am using substringcount function but don't know how to move forward .

Regards,

Shantanu

Re: Removing multiple occurances of a substring

varshavig12 — Tue, 15 Nov 2016 11:20:59 GMT

Why you want to do this ?

Like what is the objective behind this ?

Re: Removing multiple occurances of a substring

Anil_Babu_Samineni — Tue, 15 Nov 2016 11:24:46 GMT

Can you provide more values which you have. I am expecting real data. So that, we will figure out. The reason i am asking this? Suppose, Sometimes we do have different size of values.

Re: Removing multiple occurances of a substring

Tue, 15 Nov 2016 11:30:17 GMT

Hi All ,

I am concatenating 4-5 field values to form this string , 'test' coming multiple times does not make any sense . So need to reduce the final string to the first occurrence of the string 'test'.

Regards,

Shantanu

Re: Removing multiple occurances of a substring

Anil_Babu_Samineni — Tue, 15 Nov 2016 11:35:34 GMT

We can do different different ways. In fact, We will help you by using your key words as i recommend in the before Reply.

1) Replace(SubField(GetFieldSelections(Output),1),',','')

[Script]:

LOAD Welcome, SubField(Welcome,' ') as Output Inline [

Welcome

'this test is a test string test '

];

Output:

Re: Removing multiple occurances of a substring

Tue, 15 Nov 2016 11:44:09 GMT

Hi anil ,

Thanks for the reply , can you please break it down and explain the thought process behind your solution .

Regards,

Shantanu

Re: Removing multiple occurances of a substring

stigchel — Tue, 15 Nov 2016 13:10:49 GMT

Where you are concatenating, can you concatenate distinctly? See this example for New:

Data:

Load * ,Rowno() as Row Inline [Text

'this test is a test string test'

'yet another test string test'

];

Tmp:

Load *,AutoNumber(Word,Row) as Id;

Load Row,SubField(Text,' ') as Word resident Data;

New:

Load Concat(DISTINCT Word,' ',Id) as NewText Resident Tmp Group by Row;

Re: Removing multiple occurances of a substring

stigchel — Tue, 15 Nov 2016 13:11:44 GMT

And the qvw attached