topic number conversion in QlikView

number conversion

Fri, 04 Nov 2011 16:59:20 GMT

I'm trying to figure out a way to make this conversion in QV:
Analyzing Hard Disk Utilization in a CSV file:

I have three columns for different OS'es:

Disk Size Used Available

373860104 145280064 228580040 (Windows/Linux OS)

86 77.12 8.6 (AIX's IBM in GB)

1.1T 849G 229G (Solaris -Sun)

How can I standardize and represent these variations at the GB level in QV?

Re: number conversion

rrsrini2907 — Fri, 04 Nov 2011 17:41:14 GMT

Just a question.

Will the third column have the braces text?

You need to have "if else" and also "Replace"

Regards,

Srini

TAKE Solutions.

number conversion

swuehl — Fri, 04 Nov 2011 17:47:06 GMT

Hi,

is there any way to retrieve the OS per record? Could you tell if the record is for Windows/Linux or AIX?

You might just guess the OS by assuming Disk size below 1,000 or 1,000,000 and without character is given in GB, if it's larger than it's in Byte (?) and if there is a character, the character denotes the size prefix.

To transform to GB, you may use something like this: http://community.qlik.com/message/148331

But guessing the OS from disk size may be dangerous, so, is there a unambigous way to tell OS?

Regards,

Stefan

number conversion

swuehl — Sat, 05 Nov 2011 11:47:36 GMT

rovival00,

somelike suggested in my above post could look like this (demonstrated the calculation for Disk Size only):

DISKS:

LOAD * INLINE [

Disk Size, Used, Available, OS

373860104, 145280064, 228580040, (Windows/Linux OS)

86, 77.12, 8.6, (AIX's IBM in GB)

1.1T, 849G, 229G, (Solaris -Sun)

];

SPACES:

LOAD *,

num(DiskSize*Pick(Match(DiskSizeUnit,'B','K','M','G','T'),1e-9,1e-6,1e-3,1,1e3)) as DiskSizeInGB,

num(DiskSize*Pick(Match(DiskSizeUnit,'B','K','M','G','T'),pow(1024,-3),pow(1024,-2),1/1024,1,1024)) as DiskSizeInGiB;

LOAD *,

num#(KeepChar([Disk Size],'0123456789.'), '#.#', '.' , ',') as DiskSize,

if(isText([Disk Size]),KeepChar([Disk Size],'TGMKB'), if ([Disk Size]< 1e6,'G','B')) as DiskSizeUnit

resident DISKS;

drop table DISKS;

I included two ways to transform Disk sizes in given unit to GB, depending on if your logs are giving the numbers in binary or decimal prefix nomenclature (check SI vs IEC prefix for instance).

Hope this helps,

Stefan

number conversion

Mon, 07 Nov 2011 17:03:53 GMT

Thank you for much of the helpful answers. To answer some questions, the text in parens (eg windows/linux) is not included in the report. The report provides a summary of disk data and I deduce the OS based on the host name (lnxdev1 or ibmdev1). I'm going to try to solution provided by Swuehl and post an update if it works. I'll modify it slightly to exclude the text data.

Thanks

number conversion

swuehl — Tue, 08 Nov 2011 17:25:05 GMT

So you could probably do it like:

DISKS:

LOAD host,

date,

Filesystem,

DiskSize,

Used,

Available,

Capacity,

Mounted as [Mounted on]

FROM

disk_data.csv

SPACES:

LOAD *,

num(DiskSize*Pick(Match(DiskSizeUnit,'B','K','M','G','T'),1e-9,1e-6,1e-3,1,1e3)) as DiskSizeInGB,

num(DiskSize*Pick(Match(DiskSizeUnit,'B','K','M','G','T'),pow(1024,-3),pow(1024,-2),1/1024,1,1024)) as DiskSizeInGiB;

LOAD *,

num#(KeepChar([DiskSize],'0123456789.'), '#.#', '.' , ',') as DiskSize,

if(left(host,3)='sun',KeepChar([DiskSize],'TGMKB'), if (left(host,3)='ibm','G','B')) as DiskSizeUnit

resident DISKS;

and similar for Used, Available (and Capacity?).

Hope this helps,

Stefan

number conversion

Wed, 09 Nov 2011 16:29:59 GMT

Thanks a lot