topic Re: sub string in qlikview in QlikView

sub string in qlikview

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I need your help, in SQL i have a column name

Contactref from Table1 and this column1 has data such as

ColumnName CompanyName, post code, ref number company special Ref

data: Boots, M154ZZ, WD22056 BootsSpecialRef

I want to link Table 1 with Table2 and the only join between them is the ref number company special Ref (WD22056 BootsSpecialRef)

the other tables Column2 looks like this

ColumnName: Ref number company special Ref

Data: WD22056 BootsSpecialRef

In order to join these two tables, I have to get rid of the first two commas in Column1, so I want the data to be WD22056 BootsSpecialRef as column2.

Is this possible please help

Many thanks

Re: sub string in qlikview

Anonymous — Mon, 22 May 2017 14:31:03 GMT

Try

subfield(Contractref,',',3) as Column2

Re: sub string in qlikview

sergio0592 — Mon, 22 May 2017 14:32:54 GMT

Use PurgeChar() function.

How to clean a field | Qlik Community

Re: sub string in qlikview

trdandamudi — Mon, 22 May 2017 14:41:46 GMT

May be as below:

Table1:
Load
Subfield(RefNumber,',',3) as _RefNumber,
* Inline [
RefNumber
'Boots, M154ZZ,WD22056 BootsSpecialRef'
];

Table2:
Load RefNumber as _RefNumber,
* Inline [
RefNumber
WD22056 BootsSpecialRef
];

Re: sub string in qlikview

Anil_Babu_Samineni — Mon, 22 May 2017 14:43:57 GMT

Can you provide more data set. One row is not sufficient to reply.

Re: sub string in qlikview

Peter_Cammaert — Mon, 22 May 2017 14:47:56 GMT

... Trim(SubField(Contractref, ',', 3)) AS Column2, ...

But like Anil already said before, one single example is not enough to create an expression that works on all your data.

Re: sub string in qlikview

Mon, 22 May 2017 14:58:04 GMT

Hello All,

Thank you very much for all your replies, Wallo's (subfield(Contractref,',',3) as Column2) has worked for me perfectly,

is it possible to remove the first part of this as well

WD22056 BootsSpecialRef

and only show BootsSpecialRef

Re: sub string in qlikview

Peter_Cammaert — Mon, 22 May 2017 15:03:26 GMT

Like this?

... Mid(Trim(SubField(Contractref, ',', 3)), index(Trim(SubField(Contractref, ',', 3)), ' ')+1) AS Column2, ...

Re: sub string in qlikview

Anonymous — Mon, 22 May 2017 15:07:02 GMT

We would need more info for that.

Is the beginning always 6 characters? Does the 2nd part always start with 'Boots'?

Is the 2nd part always 16 characters? Is there only numbers in the first part and never the 2nd?

Assuming there is never numerics in the 2nd part and there is always a 2 character prefix, you could try this 2 step process.

purgechar(subfield(Contractref,',',3),'1234567890') as Column2

right(Column2,len(Column2)-2) as Column2a

Re: sub string in qlikview

Anonymous — Mon, 22 May 2017 15:08:19 GMT

Peter's will work great as long as there is a space between the 2 parts. You'll have to let us know.

Re: sub string in qlikview

Mon, 22 May 2017 15:17:04 GMT

Thank you very much Peter, this has worked for me as I wanted.

Re: sub string in qlikview

Tue, 23 May 2017 09:34:45 GMT

Hello All,

Peter's solution has worked great but I need to split the code as well

Boots, M154ZZ, WD22056 BootsSpecialRef

I want to get just WD22056, but the code length differ but there's always a space between the code and BootsSpecialRef

atkinsow‌ Yes Wallo, there are always 2 spaces

Re: sub string in qlikview

Peter_Cammaert — Tue, 23 May 2017 09:41:30 GMT

Like this?

... Left(Trim(SubField(Contractref, ',', 3)), index(Trim(SubField(Contractref, ',', 3)), ' ')-1) AS Column3, ...

Re: sub string in qlikview

Tue, 23 May 2017 09:54:21 GMT

Hi Peter,

This has worked great, thank you again, but would you please advise on how does this work as I might need it for the same query but with one comma instead of two commas such in this case

M154ZZ, WD22056 BootsSpecialRef

and is it possible to use an OR statement like

if column = 2 commas then get WD22056 after the second comma or if column= 1 comma then get the WD22056 after the first comma

Re: sub string in qlikview

Peter_Cammaert — Tue, 23 May 2017 10:32:50 GMT

IMHO the best way for you to see this working is by creating a number of text boxes in an empty document, and slowly building up this expression from the inside out. You can use your initial value between quotes to see what happens. For example, these are the successive expressions:

=SubField('Boots, M154ZZ, WD22056 BootsSpecialRef', ',', 3) // Grabs the third field

=Trim(SubField('Boots, M154ZZ, WD22056 BootsSpecialRef', ',', 3)) // Cuts leading and trailing spaces

=index(Trim(SubField('Boots, M154ZZ, WD22056 BootsSpecialRef', ',', 3)), ' ')-1 // Position of first space, -1 to get length of front part

// and so on...

In the future, this technique will help you for sure in analysing any string expressions with lots of nested function calls.

Yes, you can use IF() calls and OR operators to apply a different logic when there are two or three commas in your value. String function SubstringCount() will tell you how many commas are present.

Re: sub string in qlikview

Tue, 23 May 2017 10:43:22 GMT

Thank you very much again for your detail explanation Peter,

Would you please write me the script of IF and OR in my case, I have tried different ways but unable to get it right? as I'm new to this function, please help

Re: sub string in qlikview

Tue, 20 Jun 2017 14:15:08 GMT

Hello Peter,

Would you please help me here again,

Because of the nature of our database in cases we have 2 commas and in cases we have less or more

is it possible to use wildmatch in this script, I am trying to get the full word within the string that has WD either WD22056 or WD12345, and either if it is in the middle or at the end, I want to create a new column based on this script?

WildMatch(ContactRef,'*WD*',CS_ContactRef

I have tried different ways,

but none of them has worked

Please help

Re: sub string in qlikview

Anonymous — Tue, 20 Jun 2017 21:52:25 GMT

Amir,

You keep adding questions to your original question. It's better to ask the full question in the beginning.

If I understand what you're asking for now, you could try something like this. But it would only work if it ALWAYS starts with WD and ALWAYS has a space at the end of the code.

'WD' & TextBetween(ContractRef,'WD',' ') as Column X