topic Re: String which should start from the alphabetic in QlikView

String which should start from the alphabetic

tabisen1 — Fri, 14 Aug 2015 06:50:21 GMT

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

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,

Re: String which should start from the alphabetic

MK_QSL — Fri, 14 Aug 2015 06:53:19 GMT

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

Re: String which should start from the alphabetic

MayilVahanan — Fri, 14 Aug 2015 06:53:23 GMT

Can you able to specify the output?

Re: String which should start from the alphabetic

sunny_talwar — Fri, 14 Aug 2015 06:53:50 GMT

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

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

Re: String which should start from the alphabetic

tabisen1 — Fri, 14 Aug 2015 07:03:33 GMT

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.

mh04ab1234	mh04ab1234
123mwv123	mwv123
mh04ab12345678	mh04ab1234
mwv1239876	mwv123
9876mwv123	mwv123
mwv1239876234	mwv123
9876234mwv123	mwv123

Re: String which should start from the alphabetic

MayilVahanan — Fri, 14 Aug 2015 07:08:06 GMT

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;

Re: String which should start from the alphabetic

sunny_talwar — Fri, 14 Aug 2015 07:08:08 GMT

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?

mh04ab12345678

mh04ab1234

vs.

mwv1239876234

mwv123

Re: String which should start from the alphabetic

tabisen1 — Fri, 14 Aug 2015 07:13:53 GMT

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

Re: String which should start from the alphabetic

sunny_talwar — Fri, 14 Aug 2015 07:16:03 GMT

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

Re: String which should start from the alphabetic

MK_QSL — Fri, 14 Aug 2015 07:19:42 GMT

Your expected result is strange to achieve.

mh04ab12345678 = mh04ab1234

mwv1239876234 = mwv123

There are no criteria or logic which can handle this !

Re: String which should start from the alphabetic

tabisen1 — Fri, 14 Aug 2015 07:20:50 GMT

Dear Sunny,

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

Re: String which should start from the alphabetic

Anonymous — Fri, 14 Aug 2015 07:21:28 GMT

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

Re: String which should start from the alphabetic

sasiparupudi1 — Fri, 14 Aug 2015 07:24:35 GMT

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

Re: String which should start from the alphabetic

tabisen1 — Fri, 14 Aug 2015 07:25:32 GMT

Dear Manish,

Is their any criteria for this

data base	currect Truck No.
mh04/ ab 1234	mh04ab1234
123 mwv-123	mwv123
mh-04 ab1234 / 5678	mh04ab1234
mwv 123 (9876)	mwv123
9876 / mwv123	mwv123
mwv 123 (9876/234)	mwv123
9876/ 234- mwv123	mwv123

Re: String which should start from the alphabetic

tabisen1 — Fri, 14 Aug 2015 07:28:49 GMT

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 1234	mh04ab1234
123 mwv-123	mwv123
mh-04 ab1234 / 5678	mh04ab1234
mwv 123 (9876)	mwv123
9876 / mwv123	mwv123
mwv 123 (9876/234)	mwv123
9876/ 234- mwv123	mwv123

String which should start from the alphabetic

Re: String which should start from the alphabetic

MK_QSL — Fri, 14 Aug 2015 07:44:26 GMT

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')

Re: String which should start from the alphabetic

sunny_talwar — Fri, 14 Aug 2015 07:55:22 GMT

Manish it seems like that there is a / after 1234

mh-04 ab1234 / 5678

mh04ab1234

and ( after 123

mwv 123 (9876)

mwv123

Seems he is looking for things between special characters

Re: String which should start from the alphabetic

MK_QSL — Fri, 14 Aug 2015 07:59:52 GMT

If I will consider that then

mh-04 ab1234 / 567 = mh04ab1234

mh04/ ab 1234 = mh04

Re: String which should start from the alphabetic

MK_QSL — Fri, 14 Aug 2015 08:02:15 GMT

Try this...

IF(LEN(KeepChar(

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

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

KeepChar(

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

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

KeepChar(

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

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

Re: String which should start from the alphabetic

sunny_talwar — Fri, 14 Aug 2015 08:04:22 GMT

Ya you are right. Some logic is needed here