data in one format

[#1]

i have data in different format for field1

for ex:-1mb,2MB,3MBPS,34 kb,98KBPS,6gb,etc

but i want it one format i.e in MBPS  i need to convert kb to mb and gb to mb

and mbps =mb same for kbps=kb

Please suggest

[jsingh71]

You can use " if.." during load time and convert it as you want. If possible then share some sample data.

[senpradip007]

Hi.

Can you post a sample application?

[SunilChauhan]

Try this

Replace(Replace(Upper(Field1),'KBPS','KB'),'MBPS','MB') as Fieldname

[PrashantSangle]

Hi,

Try this,

If(Upper(Left(PurgeChar(FieldName,'1234567890'),2))='KB',Left(FileldName,2)*1024)

OR

If(Upper(Left(PurgeChar(FieldName,'1234567890'),2))='KB',KeepChar(FieldName,'1234567890.')*1024)

Regards,

[#6]

plz refer image

[MK_QSL · Accepted solution]

You can covert using below in your script..

Load

  IF(Index(UPPER(Replace(SIZE,' ','')),'MB')>1,Left(UPPER(Replace(SIZE,' ','')),Index(UPPER(Replace(SIZE,' ','')),'MB')-1),

  IF(Index(UPPER(Replace(SIZE,' ','')),'MBPS')>1, Left(UPPER(Replace(SIZE,' ','')),Index(UPPER(Replace(SIZE,' ','')),'MBPS')-1),

  IF(Index(UPPER(Replace(SIZE,' ','')),'GB')>1, Left(UPPER(Replace(SIZE,' ','')),Index(UPPER(Replace(SIZE,' ','')),'GB')-1)*1024,

  IF(Index(UPPER(Replace(SIZE,' ','')),'KB')>1, Left(UPPER(Replace(SIZE,' ','')),Index(UPPER(Replace(SIZE,' ','')),'KB')-1)/1024,

  IF(Index(UPPER(Replace(SIZE,' ','')),'KBPS')>1, Left(UPPER(Replace(SIZE,' ','')),Index(UPPER(Replace(SIZE,' ','')),'KBPS')-1)/1024)))))&'MB' as SIZE

Inline

[

  SIZE

  6.75Mb

  6.81 Mbps

  6.756 MB

  2048 KB

  1024KBPS

  1gb

];

[#8]

i also want mbps return

[PrashantSangle]

Hi,

This is only calculation logic you can append MBPS using & ' MBPS' in above expression.

Regards,

[#10]

thnx it worked...