Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

tabisen1
Not applicable

String which should start from the alphabetic

Dear All,

Very good after noon to all i am Creating the QVW for the count of truck round in which i am facing some issues for example.

truck no. should be

mh04ab1234

mwv123

ect,

I data base the truck no. is not as per the format. I want to create it like above format. I have  cleared the special charater in between the truck no and created the single string which show like this

Previous

mh04/ ab 1234

123 mwv-123

mh-04 ab1234 / 5678

mwv 123 (9876)

9876 / mwv123

mwv 123 (9876/234)

9876/ 234- mwv123

current by using the

PurgeChar([Truck no],'/, ,-,(,)')

mh04ab1234

123mwv123

mh04ab12345678

mwv1239876

9876mwv123

mwv1239876234

9876234mwv123


I want the above current to modify which will take the starting alphabet and remove the starting numbers and also want to remove the end extra number which is extra number for the reference in give at the end and begining of the truck no.



Regards,





Tags (1)
1 Solution

Accepted Solutions
MK_QSL
Not applicable

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

22 Replies
MK_QSL
Not applicable

Re: String which should start from the alphabetic

For all of the above String, can you provide the solution also which you are looking for?

mayilvahanan
Not applicable

Re: String which should start from the alphabetic

Hi

Can you able to specify the output?

sunny_talwar
Not applicable

Re: String which should start from the alphabetic

Are you trying to just keep letters and remove everything else? May be this:

Trim(KeepChar(FieldName, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')) as NewFieldName

tabisen1
Not applicable

Re: String which should start from the alphabetic

Dear Sunny,

Thank you for the reply but your formula is giving the

Trim(KeepChar(FieldName, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'))

mhab

mwv

I want the result in correct format which is shown in the below table with the wrong.

  

Wrong truck no.correct Truck No.
mh04ab1234mh04ab1234
123mwv123mwv123
mh04ab12345678mh04ab1234
mwv1239876mwv123
9876mwv123mwv123
mwv1239876234mwv123
9876234mwv123mwv123
mayilvahanan
Not applicable

Re: String which should start from the alphabetic

Hi

Try like this

MappingTable:

Mapping load * inline

[

TruckNo, CorrectTruckNo

mh04ab1234,mh04ab1234

123mwv123,mwv123

mh04ab12345678,mh04ab1234

mwv1239876,mwv123

9876mwv123,mwv123

mwv1239876234,mwv123

9876234mwv123,mwv123

];

Load ApplyMap('MappingTable', TruckNo, Null()) AS TruckNo from tablename;

sunny_talwar
Not applicable

Re: String which should start from the alphabetic

I am having a hard time understanding the logic here. I understand that you don't want the numbers at the front, but how are you truncating numbers at the end for some of them?

mh04ab12345678mh04ab1234

vs.

mwv1239876234mwv123
tabisen1
Not applicable

Re: String which should start from the alphabetic

dear sunny,

I don't want the number at the start and that the end. I want only the truck no.

Dear Mayil,

The List of the Truck no. is large and i don't know the all truck no but to that the mapping function will not work

sunny_talwar
Not applicable

Re: String which should start from the alphabetic

Is there a place in your database which contain a list of all the truck no???

MK_QSL
Not applicable

Re: String which should start from the alphabetic

Your expected result is strange to achieve.

mh04ab12345678 = mh04ab1234

mwv1239876234 = mwv123

There are no criteria or logic which can handle this !