Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
You can use " if.." during load time and convert it as you want. If possible then share some sample data.
Hi.
Can you post a sample application?
Try this
Replace(Replace(Upper(Field1),'KBPS','KB'),'MBPS','MB') as Fieldname
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,
plz refer image
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
];
i also want mbps return
Hi,
This is only calculation logic you can append MBPS using & ' MBPS' in above expression.
Regards,
thnx it worked...