Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mslottje
Contributor II

Searching text string within field in table

Hi experts,

I'm facing the following problem and would like to ask you experts for help.

In a table I have a document number that contains 4 fixed characters (red) and 5 random characters (blue), i.e. IB17-0123.

Let's call this table the IB table.

Next to the IB table, I have a quite incomplete table which I want to left join with the IB table.

This incomplete table doesn't have a unique document number that could be used as a key field to match with the IB table.

My only option is a description field where some details as name, date and IB are mentioned (manually).

Unfortunately the order of these details are not always the same.

So, "Name, IB number, date" or "IB number, Name, date" or "date, Name, IB number" etc.

I'm looking for a function which searches for the complete IB number in the description and excludes the other characters (details) in this field.

In this way, I want to create a field that could be used as a key field for the left join in my loading script.

How should such a function look a like?

Please note that I'm aware of the fact this is far from ideal and that a solution in our system/database would be much safer and easier.

Let me know if I should clarify somethingSmiley Happy

Cheers,

Mike

1 Solution

Accepted Solutions
stigchel
Honored Contributor

Re: Searching text string within field in table

You can use the Index function to locate the 'IB' string and from there take 9 characters

Example:

=mid('Name, IB17-0123, Date',Index('Name, IB17-0123, Date','IB'),9)

with a field

Load ...

mid(Details,Index(Details,'IB'),9) as Key

From ...

8 Replies
tomasz_tru
Valued Contributor

Re: Searching text string within field in table

Looks like a problem for regular expression - and it's not included in Qlik Sense. What's the data source? Maybe it's possible to clean the data before loading to QS.

Tomasz

mslottje
Contributor II

Re: Searching text string within field in table

Thanks for your reply Tomasz!

We're using a SQL database.

Do you have any idea how we should do this?

Mike

tomasz_tru
Valued Contributor

Re: Searching text string within field in table

It depends on what's the database - some support regex natively, some doesn't.

Example regex for such a case : '[1].[-]....'   You can use https://regex101.com/ for validation on bigger data sample.

Tomasz

stigchel
Honored Contributor

Re: Searching text string within field in table

You can use the Index function to locate the 'IB' string and from there take 9 characters

Example:

=mid('Name, IB17-0123, Date',Index('Name, IB17-0123, Date','IB'),9)

with a field

Load ...

mid(Details,Index(Details,'IB'),9) as Key

From ...

mslottje
Contributor II

Re: Searching text string within field in table

Thanks Piet Hein!

I understand what you're doing, but I don't understand where the Details field is coming from.

Should I use the original name of my table field in that place?

beck_bakytbek
Valued Contributor III

Re: Searching text string within field in table

Hi Mike,

check this:

1.https://www.youtube.com/watch?v=rIsbfthlTE4

2.https://www.youtube.com/watch?v=dXwqg0Ktj9g

you can delegate these techniques in Qlik Sense

i hope that helps

Beck

stigchel
Honored Contributor

Re: Searching text string within field in table

Yes, I didn't know the name of your field

mslottje
Contributor II

Re: Searching text string within field in table

Yeah I thought so! I've tried your suggestion and it works!Smiley Happy

Thanks a lot!

Community Browser