sub string in qlikview

[#1]

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

[Anonymous · Accepted solution]

Try

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

[sergio0592]

Use PurgeChar() function.

How to clean a field | Qlik Community

[trdandamudi]

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
];

[Anil_Babu_Samineni]

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

[Peter_Cammaert]

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

[#7]

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

[Peter_Cammaert]

Like this?

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

[Anonymous]

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

[Anonymous]

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