Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
tabisen1
New Contributor

Re: String which should start from the alphabetic

Dear Sunny,

No I am not having the List of all truck no.

Highlighted
neetha_p
Honored Contributor

Re: String which should start from the alphabetic

Hi Tarun,


Does your CorrectTruckNo has any limited number of digits at the end.

is there any other field in database which gives truck nos?


Regards

Neetha

Highlighted
sasiparupudi1
Honored Contributor III

Re: String which should start from the alphabetic

please provide your data file..but at this stage, there is not enough information available to extract the strings that you are looking for

Highlighted
tabisen1
New Contributor

Re: String which should start from the alphabetic

Dear Manish,

Is their any criteria for this

  

data basecurrect Truck No.
mh04/ ab 1234mh04ab1234
123 mwv-123mwv123
mh-04 ab1234 / 5678mh04ab1234
mwv 123 (9876)mwv123
9876 / mwv123mwv123
mwv 123 (9876/234)mwv123
9876/ 234- mwv123mwv123
Highlighted
tabisen1
New Contributor

Re: String which should start from the alphabetic

Dear Sasidhar,

My data file has the field named truck no.

Data file field look like this

Which Should be currect like this


mh04/ ab 1234mh04ab1234
123 mwv-123mwv123
mh-04 ab1234 / 5678mh04ab1234
mwv 123 (9876)mwv123
9876 / mwv123mwv123
mwv 123 (9876/234)mwv123
9876/ 234- mwv123mwv123

Highlighted
MVP
MVP

Re: String which should start from the alphabetic

I am close to it but can't get logic behind MWV123 and mh04ab1234.

Why only three letter 123 after MWV and 4 letters after ab???

KeepChar(

IF(FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(') > 0,

Left(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/')-1),

Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','')),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789')

Highlighted

Re: String which should start from the alphabetic

Manish it seems like that there is a / after 1234

mh-04 ab1234 / 5678mh04ab1234

and ( after 123

mwv 123 (9876)mwv123

Seems he is looking for things between special characters

Highlighted
MVP
MVP

Re: String which should start from the alphabetic

If I will consider that then

mh-04 ab1234 / 567 = mh04ab1234

mh04/ ab 1234 = mh04

Highlighted
MVP
MVP

Re: String which should start from the alphabetic

Try this...

IF(LEN(KeepChar(

IF(FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/') > 0,

Left(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/')-1),

Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','')),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'))<6,

KeepChar(

IF(FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(') > 0,

Left(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(')-1),

Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','')),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'),

KeepChar(

IF(FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/') > 0,

Left(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),FindOneOf(Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ',''),'(/')-1),

Replace(Mid(TruckNO,FindOneOf(TruckNO,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')),' ','')),'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'))

Capture.PNG

View solution in original post

Highlighted

Re: String which should start from the alphabetic

Ya you are right. Some logic is needed here