Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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 something:)

Cheers,

Mike

1 Solution

Accepted Solutions
stigchel
Partner - Master
Partner - Master

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 ...

View solution in original post

8 Replies
tomasz_tru
Specialist
Specialist

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

Anonymous
Not applicable
Author

Thanks for your reply Tomasz!

We're using a SQL database.

Do you have any idea how we should do this?

Mike

tomasz_tru
Specialist
Specialist

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
Partner - Master
Partner - Master

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 ...

Anonymous
Not applicable
Author

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
Master
Master

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
Partner - Master
Partner - Master

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

Anonymous
Not applicable
Author

Yeah I thought so! I've tried your suggestion and it works!:)

Thanks a lot!