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

[#2]

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

[#3]

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

[Peter_Cammaert]

Like this?

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

[#5]

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

[Peter_Cammaert]

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.

[#7]

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

[#8]

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

[Anonymous]

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