topic Re: Convert from .xls to .qvd in QlikView

Convert from .xls to .qvd

Anonymous — Wed, 21 Sep 2011 13:56:29 GMT

Hi,

I'm working in a model that gets the data from some .xls files and I want to store them as .qvd.

Also what happens if the Excel book has many sheets, do I have to load each one? knowing that there is a master sheet where all the cells are routed to another cell in another sheet.

Thanks a lot!

Convert from .xls to .qvd

SunilChauhan — Wed, 21 Sep 2011 13:59:04 GMT

tab1:

Load 8,

from path/xls

store tab1 into your location/abc.qvd

ya i believe its require to load all excel

Convert from .xls to .qvd

rwunderlich — Wed, 21 Sep 2011 14:00:46 GMT

LOAD the xls files. Write them to QVDs using the STORE statement.

Each excel sheet is loaded with a seperate LOAD statement. When you load an excel sheet, cell formulas are resolved. So if all the results exist in a single sheet, that is all you have to load.

-Rob

http://robwunderlich.com

Convert from .xls to .qvd

Anonymous — Wed, 21 Sep 2011 14:07:25 GMT

Hi,

so I have this code:

LOAD NIT as A02.nit_cliente,

CLIENTE,

FACTURA,

FECHA,

[VALOR NETO],

CARGOS,

[TOTAL NETO],

IVA,

[TOTAL FACTURA],

SUC,

REG,

GC,

RET.,

ICO

FROM

(biff, embedded labels, table is INICIAL$);

Then I want to STORE it. Where should I add the STORE statement?

And I have a sheet wit a lot of formulas refering to other sheets, as I understood you, I just have to call that sheet with the formulas?

Re: Convert from .xls to .qvd

its_anandrjs — Wed, 21 Sep 2011 14:14:03 GMT

Hi,

Write code some thing like

TableName:

LOAD NIT as A02.nit_cliente,

CLIENTE,

FACTURA,

FECHA,

[VALOR NETO],

CARGOS,

[TOTAL NETO],

IVA,

[TOTAL FACTURA],

SUC,

REG,

GC,

RET.,

ICO

FROM

(biff, embedded labels, table is INICIAL$);

Store TableName into TableName.qvd;

The name of table is important always give some name

Always when you make qvd it is store in your qvw file location.

let me know about this

Rgds

Anand

Convert from .xls to .qvd

Anonymous — Wed, 21 Sep 2011 14:24:36 GMT

Hi,

ok I've already done what you told me. It worked great.

Now if I have a different distribution of the folders and I have a folder called ../03 Qvd/ and in the "Main" tab I have this code SET vRutaDatos = '..\03 Qvd\'; how can I uste the STORE statement to store the .qvd in the folder desired.

Thnx!

Re: Convert from .xls to .qvd

its_anandrjs — Wed, 21 Sep 2011 14:56:35 GMT

Hi,

You have to write a code like

Store TableName into TableName.qvd;

Store TableName into $(vRutaDatos)\TableName.qvd;

Let me know about this.

HTH

Rgds

Anand

Re: Convert from .xls to .qvd

its_anandrjs — Wed, 21 Sep 2011 15:05:34 GMT

Hi,

Your location like

SET vRutaDatos = "..\03 Qvd\";

after load statement

Store TableName into $(vRutaDatos)\TableName.qvd;

HTH

Rgds

Anand

Convert from .xls to .qvd

rwunderlich — Wed, 21 Sep 2011 15:24:20 GMT

Store TableName into [$(vRutaDatos)\TableName.qvd]

It's always a good practice to include square brackets around the file path. In this case the brackets are required because vRutaDatos contains spaces.

-Rob

http://robwunderlich.com

Re: Convert from .xls to .qvd

its_anandrjs — Wed, 21 Sep 2011 15:35:13 GMT

Hi Rob,

Yes it is good practice but I thing it is not required because we are store path into variables like

Set Path = "c:\Folder\a b";

here a b is folder with spces

and we use it in store statement

Store TableName into $(Path)\TableName.qvd;

Rgds

Anand

Convert from .xls to .qvd

rwunderlich — Wed, 21 Sep 2011 15:54:48 GMT

Anand,

I stand corrected. I wasn't aware that it would work this way.

-Rob