topic Re: data in one format in QlikView

data in one format

Tue, 18 Mar 2014 06:40:50 GMT

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

Re: data in one format

jsingh71 — Tue, 18 Mar 2014 06:45:22 GMT

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

Re: data in one format

senpradip007 — Tue, 18 Mar 2014 06:48:17 GMT

Hi.

Can you post a sample application?

Re: data in one format

SunilChauhan — Tue, 18 Mar 2014 06:55:39 GMT

Try this

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

Re: data in one format

PrashantSangle — Tue, 18 Mar 2014 07:12:31 GMT

Hi,

Try this,

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

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

Regards,

Re: data in one format

Tue, 18 Mar 2014 07:12:34 GMT

plz refer image

Re: data in one format

MK_QSL — Tue, 18 Mar 2014 07:15:32 GMT

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

];

Re: data in one format

Tue, 18 Mar 2014 07:17:11 GMT

i also want mbps return

Re: data in one format

PrashantSangle — Tue, 18 Mar 2014 07:19:11 GMT

Hi,

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

Regards,

Re: data in one format

Tue, 18 Mar 2014 13:53:54 GMT

thnx it worked...