topic Re: Script: cannot reload until column AS from the excel in QlikView

Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 10:55:41 GMT

Hi Qommunity!

I'm trying to load an Excel file, where there are no headers, and I want to pick information until the column BN of the excel.

However, when I launch the reload, I always have this error: "Field not found - <AS>". When I comment this field, it says this is the next which cannot be found (AT).

My script is the following:

CS_SLA_1:

CrossTable(SLA_FECHA, SLA_NIVEL, 😎

LOAD

'UK' AS PAÍS,

'0000' AS COD_ENTIDAD_1,

A AS SLA_MODELO_OPERATIVO,

B AS SLA_DEPARTAMENTO,

C AS SLA_FUNCIÓN,

D AS SLA_SERVICIO,

E AS SLA_INDICADOR,

// F,

// G,

// H,

// I,

// J,

// K,

// L,

// M,

// N,

// O,

// P,

// Q,

// R,

S AS SLA_TIPO,

// T,

// U,

// V,

// W,

// X,

// Y,

// Z,

// AA,

// AB,

// AC,

// AD,

AE AS [41640],

AF AS [41671],

AG AS [41699],

AH AS [41730],

AI AS [41760],

AJ AS [41791],

AK AS [41821],

AL AS [41852],

AM AS [41883],

AN AS [41913],

AO AS [41944],

AP AS [41974],

AQ AS [42005],

AR AS [42036],

AS AS [42064],

AT AS [42095],

AU AS [42125],

AV AS [42156],

AW AS [42186],

AX AS [42217],

AY AS [42248],

AZ AS [42278],

BA AS [42309],

BB AS [42339],

BC AS [42370],

BD AS [42401],

BE AS [42430],

BF AS [42461],

BG AS [42491],

BH AS [42522],

BI AS [42552],

BJ AS [42583],

BK AS [42614],

BL AS [42644],

BM AS [42675],

BN AS [42705]

FROM

[*UK*.xlsx]

(ooxml, no labels, header is 4 lines, table is [UK New]);

Do you know if this is a QV limitation?

Thanks a lot in advance for your help!

Regards,

Vincent

Re: Script: cannot reload until column AS from the excel

Anonymous — Wed, 05 Aug 2015 10:58:50 GMT

Try to use

"AS" as ....

"AT" as ...

...

Re: Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 11:03:41 GMT

Thank a lot for your help Michael, but unfortunately, it didn't work 😞

Re: Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 11:06:07 GMT

You could try [AS]

Re: Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 11:13:13 GMT

Thanks a lot Aidan, but no luck neither 😞

Re: Script: cannot reload until column AS from the excel

Digvijay_Singh — Wed, 05 Aug 2015 11:13:38 GMT

Try 'AS'

Re: Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 11:14:06 GMT

Hi Vincent,

you are doing a wildcard load of all the UK excels, are you sure for all of those files the fields do in fact exist and have data?

Try testing your code on a single excel which you know has all the columns populated first

hope that helps

Joe

Re: Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 11:16:10 GMT

You could try Load * from the excel file.

Store this off into a qvd and re-load the data from the qvd and try renaming the columns possibly?

Re: Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 11:23:57 GMT

Thanks Digvijay, but doing this, it changes the content of my column in text AS.

Re: Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 11:28:39 GMT

Hi Joe,

I'm doing a wildcard load but there is only one UK file. The problem I have is that every month, the user will add a new column with the data of the month, and my idea was to prevent this problem by loading all the columns with already the date written. For this, I need a generic name which is the name of the excel column. Today, there is information available until the column AV.

Anyway, many thanks for your help!

Re: Script: cannot reload until column AS from the excel

Digvijay_Singh — Wed, 05 Aug 2015 11:33:56 GMT

I tried below in personal edition ver 11, its fetching values propely, -

Directory;

LOAD AS as [42064],

AT as [42095]

FROM

[..\..\..\175379.xlsx]

(ooxml, no labels, table is Sheet1);

Re: Script: cannot reload until column AS from the excel

Wed, 05 Aug 2015 11:37:07 GMT

I was wrong, I have 2 UK excels files, and one of them has data until the column AR...

Now I've found the problem, I'll try to find a solution, because my initial solution is not valid anymore.

Thanks a lot Joe!

Re: Script: cannot reload until column AS from the excel

sasiparupudi1 — Wed, 05 Aug 2015 11:55:09 GMT

I think your problem is *UK*.xlsx

One of your UK* file is not having all the specified columns and your script fails. It is not a qlikview limitation and qlikview can load AS as column1 and AT as Column2

HTH

Sasi