topic Re: How to find multiple occurrence of a string in a field? in QlikView

How to find multiple occurrence of a string in a field?

apoorvasd — Tue, 10 Jul 2018 13:20:08 GMT

Hello Everyone,

I have a field which has values as shown below. I am trying to fetch the string which is between, '"href="' and '"><img"', i.e., the path of a particular file. But this string can contain more than one "href" value in a single line as shown in below picture. So how can I manage to get all strings between "href" and "img" here?

When a particular value in this "link" field has only one occurrence of "href", i can use subfield function to get the string. But the problem is when it has more that one "href" value in the same line as shown in second picture above.

Any help on this?

Thank you.

Re: How to find multiple occurrence of a string in a field?

sunny_talwar — Tue, 10 Jul 2018 13:27:23 GMT

If the occurrence is twice, do you want to pull it twice or do you need 1st or 2nd occurrence only?

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Tue, 10 Jul 2018 14:39:18 GMT

If the occurrence is twice, i would want to pull it twice. Similarly, if it is thrice, i would want to pull it thrice and so on.

Thank you.

Re: How to find multiple occurrence of a string in a field?

sunny_talwar — Tue, 10 Jul 2018 14:42:49 GMT

Pull them twice or thrice in the same row or multiple rows?

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Tue, 10 Jul 2018 14:47:20 GMT

May be same row

Re: How to find multiple occurrence of a string in a field?

sunny_talwar — Tue, 10 Jul 2018 15:44:13 GMT

Try this

Table:

LOAD RowNum,

Concat(Field, ',') as Field

Group By RowNum;

LOAD RecNo() as RowNum,

TextBetween(Field, 'href="', '"><img', IterNo()) as Field

While IterNo() <= SubStringCount(Field, 'href="');

LOAD * INLINE [

Field

'abc href="adhsdbsh"><img'

'hdakddhsjd href=" admvfjsjf"><img ahdhsjdhs href=" alkfsmfh "><img gfhsghfg href="adaaaaaa"><img ahdgsahd'

];

In case you need this in multiple rows... try this

Table:

LOAD RecNo() as RowNum,

TextBetween(Field, 'href="', '"><img', IterNo()) as Field

While IterNo() <= SubStringCount(Field, 'href="');

LOAD * INLINE [

Field

'abc href="adhsdbsh"><img'

'hdakddhsjd href=" admvfjsjf"><img ahdhsjdhs href=" alkfsmfh "><img gfhsghfg href="adaaaaaa"><img ahdgsahd'

];

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Wed, 11 Jul 2018 05:11:15 GMT

Hi Sunny,

Thank you very much! This was what i wanted. But, there are other fields in my table along with this, when i implement this code, other fields are not being pulled. I see only fields, "RowNum" and "Field". Can you please help me here?

Thank you.

Re: How to find multiple occurrence of a string in a field?

sunny_talwar — Wed, 11 Jul 2018 10:34:18 GMT

Sure, can you share the script you tried running?

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Wed, 11 Jul 2018 10:47:28 GMT

PFA!

I have added an additional field named as "id" to your script in inline table. When i reload this app, i am not able to find this field.

Re: How to find multiple occurrence of a string in a field?

sunny_talwar — Wed, 11 Jul 2018 10:54:28 GMT

Unless you add id to the preceding loads, not sure how you expect it to show up after the reload is completed

Table:

LOAD RowNum,

id,

Concat(Field, ',') as Field

Group By RowNum, id;

LOAD RecNo() as RowNum,

id,

TextBetween(Field, 'href="', '"><img', IterNo()) as Field

While IterNo() <= SubStringCount(Field, 'href="');

LOAD * INLINE [

Field, id

'abc href="adhsdbsh"><img', 1

'hdakddhsjd href=" admvfjsjf"><img ahdhsjdhs href=" alkfsmfh "><img gfhsghfg href="adaaaaaa"><img ahdgsahd', 2

];

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Wed, 11 Jul 2018 11:13:57 GMT

Oh yeah, right! thanks for identifying that. The code works now!

But why is that I have to provide whole set of field names in the group by clause for it work?

Thank you.

Re: How to find multiple occurrence of a string in a field?

sunny_talwar — Wed, 11 Jul 2018 11:22:00 GMT

Because, fortunately or unfortunately, that is how the Group By clause work in QlikView.... in order to use Concat (or sum, min, max) you need to add all your non-aggregating fields to the Group By statement

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Wed, 11 Jul 2018 12:17:37 GMT

Hmm...okay, thank you very much for helping me on this topic!

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Tue, 17 Jul 2018 11:19:28 GMT

Hi Sunny,

I have an additional question in this topic. I am trying to create a link in the table. As you have shown how to pull multiple occurrences of the strings in same row, I would be converting them to be displayed as a link (using the code, 'link<url>'& Field in my expression) . So, is it possible to have two links in the same row (as we have two strings in the same row)?

Thank you.

Re: How to find multiple occurrence of a string in a field?

sunny_talwar — Tue, 17 Jul 2018 11:22:33 GMT

I would break them into multiple rows (like I showed above) for the purposes on the link table usage

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Tue, 17 Jul 2018 12:25:21 GMT

Yes, having them in multiple rows works, but I wanted them in same row. Is that possible?

Re: How to find multiple occurrence of a string in a field?

sunny_talwar — Tue, 17 Jul 2018 12:29:05 GMT

I don't think so

Re: How to find multiple occurrence of a string in a field?

apoorvasd — Tue, 17 Jul 2018 12:29:45 GMT

Okay, thank you.