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,
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'))
For all of the above String, can you provide the solution also which you are looking for?
Hi
Can you able to specify the output?
Are you trying to just keep letters and remove everything else? May be this:
Trim(KeepChar(FieldName, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')) as NewFieldName
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 |
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;
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 |
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
Is there a place in your database which contain a list of all the truck no???
Your expected result is strange to achieve.
mh04ab12345678 = mh04ab1234
mwv1239876234 = mwv123
There are no criteria or logic which can handle this !