Searching text string within field in table

[Anonymous]

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

[tomasz_tru]

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]

Thanks for your reply Tomasz!

We're using a SQL database.

Do you have any idea how we should do this?

Mike

[tomasz_tru]

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 · Accepted solution]

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]

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]

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]

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

[Anonymous]

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

Thanks a lot!