number conversion

[#1]

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?

[rrsrini2907]

Just a question.

Will the third column have the braces text?

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

Regards,

Srini

BI

TAKE Solutions.

[swuehl]

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

[swuehl]

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

[#5]

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

[swuehl]

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

[#7]

Thanks a lot