topic Variable and Excel.... in QlikView

Variable and Excel....

Tue, 21 Aug 2012 15:31:22 GMT

Hi,

I am reading in the Enclosed Excel Sheet (sheet called "Delivery")

I want to be able to read some Columns, advance a few (having data I'm not interested in) and read another few.

In the past I have been able to do that using variables, but for some reason, today, I cant make it work

I want to read in the Day, Distance etc for each Registration no (eg 'WLC729GP'). The registration part reading, works fine.

(My code also included.)

I get the message...Field not found @1....

What am I doing wrong?

All help appreciated

Thank you

Re: Variable and Excel....

Wed, 22 Aug 2012 06:42:01 GMT

If you load the excel-file directly to have a look on the field names, you will get the following

Directory;

LOAD [VEHICLE 1],

     F2,

     F3,

     F4,

     F5,

     F6,

     F7,

     [VEHICLE 2],

     F9,

     F10,

     F11,

     F12,

     F13,

     F14,

     [VEHICLE 3],

     F16,

     F17,

     F18,

     F19,

     F20,

     F21,

     [VEHICLE 4],

     F23,

     F24,

     F25,

     F26,

     F27,

     F28,

     [VEHICLE 5],

     F30,

     F31,

     F32,

     F33,

     F34

FROM

[501_LOGBOOK_JULY12 V.2.xlsx]

(ooxml, embedded labels, table is DELIVERY);

So, there is really no field @1, they all are named with F or somethimes Vehicle if you renamed it. Maybe you have to load this without field names, but then you will perhaps get A, B, C, ...

Regards Vicky

Re: Variable and Excel....

Wed, 22 Aug 2012 06:50:30 GMT

Hello Vicky,

I have tried loading them without field names, then I do get an A, or B.

Reading the older .xls files (biff) one would get

@1,

@2 etc. and you could manipulate these values with variables.

With .xlsx, you get A, B C etc, and you cannot manipulate

Re: Variable and Excel....

Wed, 22 Aug 2012 07:11:05 GMT

Hi,

But if you add a row before the first line in excel and manipulate the cells with a space, then you would have F instead of @ which you can manipulate.

Regards

Re: Variable and Excel....

Wed, 22 Aug 2012 07:28:27 GMT

Thats the thing - how do I manipulate 'F' - eg how do I add 7 columns to 'F'.