Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

data in one format

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

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

];

View solution in original post

9 Replies
jsingh71
Partner - Specialist
Partner - Specialist

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

senpradip007
Specialist III
Specialist III

Hi.

Can you post a sample application?

SunilChauhan
Champion II
Champion II

Try this

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

Sunil Chauhan
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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

plz refer image

MK_QSL
MVP
MVP

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

];

Not applicable
Author

i also want mbps return

PrashantSangle

Hi,

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

thnx it worked...