Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Dear Sunny,
No I am not having the List of all truck no.
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
please provide your data file..but at this stage, there is not enough information available to extract the strings that you are looking for
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 |
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 |
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')
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
If I will consider that then
mh-04 ab1234 / 567 = mh04ab1234
mh04/ ab 1234 = mh04
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'))
Ya you are right. Some logic is needed here