topic Loading Data form .xls file in QlikView

Loading Data form .xls file

Sat, 10 Sep 2011 05:48:29 GMT

Hi...

I am usnig a .xls file to load data in to QlickView. The .xls file has a column which is named as "Left Space" and the values in this column are like (12TB, 2.3GB, 0B, 4.37MB,............). My question is, how can I convert these values in to one single unit (lets say in gigabyte (GB))?

Thanks and Regards

Asad

Loading Data form .xls file

swuehl — Sat, 10 Sep 2011 17:25:47 GMT

Hi asadimehmud,

maybe like this:

INPUT:

LOAD * INLINE [

Left Space

12TB

2.3GB

4.37MB

];

SPACES:

LOAD *,

num(Amount*Pick(Match(Unit,'B','KB','MB','GB','TB'),1e-9,1e-6,1e-3,1,1e3)) as SpaceInGB;

LOAD *,

num#(KeepChar([Left Space],'0123456789.'), '#.#', '.' , ',') as Amount,

KeepChar([Left Space],'TGMKB') as Unit

resident INPUT;

Regards,

Stefan

Loading Data form .xls file

Thu, 29 Sep 2011 15:06:50 GMT

Thanks a lot Stefan: My data not included just the numbers above (that was just an example).... Any ways I got the other way around and did some work inside my .xls file and got my data ready to be used in QlickView...

Thanks for your suggestion and help

Regards

Asad

Loading Data form .xls file

its_anandrjs — Thu, 29 Sep 2011 15:20:18 GMT

Hi,

I suggest if you created a column and divide it by 1024 and again 1024 to bytes then you get GB value

if you want MB value so divide it once by 1024 then get MB value.

Rgds

Anand