topic Re: Error: Garbage after statement in App Development

Error: Garbage after statement

Tue, 31 May 2016 14:31:25 GMT

I can't find the error in:

LOAD

    Título,

    "Data de início" as DataI,

    "Data limite" as DataF,

    "Status Final" as Status,

    Gestor as Champion,

    "ID Status" as IDS

FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Status Ações.xlsx]

(ooxml, embedded labels, table is [BD])

I get the message: Garbage after statement

Anyone knows how to fix it or what could be causing it?

Thank you in advance.

Re: Error: Garbage after statement

oknotsen — Tue, 31 May 2016 15:49:04 GMT

What was the line of code BEFORE this LOAD statement?

Note that this load statement is missing a semi-colon at the end (which also might explain your error).

Re: Error: Garbage after statement

Tue, 31 May 2016 16:45:48 GMT

This is what came before the LOAD:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

SET LongMonthNames='janeiro;fevereiro;março;abril;maio;junho;julho;agosto;setembro;outubro;novembro;dezembro';

SET LongDayNames='segunda-feira;terça-feira;quarta-feira;quinta-feira;sexta-feira;sábado;domingo';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='pt-BR';

LOAD

    Aeroporto,

    Nível,

    "Função Superior",

    Função,

    Gestor as Champion,

    Month(Mês) as Mês,

    Ano,

    "Tipo de Meta",

    "Valor Alvo",

    "Valor Real",

    "Peso da Meta",

    "% Atingido",

    "Peso Final",

    MêsID,

    MetaID

FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Base Gestão de Metas 2016.xlsx]

(ooxml, embedded labels, table is [BD Metas Consolidadas])

Do you mind showing me where the semicolon goes? I'm sorry, I just recently started to use Qlik.

Thank you for the help!

Re: Error: Garbage after statement

Tue, 31 May 2016 16:52:49 GMT

Oh, ok, I just saw where the semicolons were missing and added them (after the FROM sentences).

Now I got a new error:

Ocorreram os seguintes erros:

Unknown Error

O erro ocorreu aqui:

LOAD Título, "Data de início" as DataI, "Data limite" as DataF, "Status Final" as Status, Gestor as Champion, "ID Status" as IDS FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Status Ações.xlsx] (ooxml, embedded labels, table is Sheet0$)

Re: Error: Garbage after statement

isaaclin — Tue, 31 May 2016 16:53:50 GMT

I think it is something wrong before this statement. Could you post more code before this statement?

Re: Error: Garbage after statement

isaaclin — Tue, 31 May 2016 18:33:37 GMT

I don't think you should add semicolon after from statement. I think something wrong with your first statement, could you comment out this part below, and try load your data? see what message you get?

LOAD

Título,

"Data de início" as DataI,

"Data limite" as DataF,

"Status Final" as Status,

Gestor as Champion,

"ID Status" as IDS

FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Status Ações.xlsx]

(ooxml, embedded labels, table is [BD])

Re: Error: Garbage after statement

Tue, 31 May 2016 19:00:40 GMT

Sure, so this is my complete code:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

SET LongMonthNames='janeiro;fevereiro;março;abril;maio;junho;julho;agosto;setembro;outubro;novembro;dezembro';

SET LongDayNames='segunda-feira;terça-feira;quarta-feira;quinta-feira;sexta-feira;sábado;domingo';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='pt-BR';

LOAD

    Aeroporto,

    Nível,

    "Função Superior",

    Função,

    Gestor as Champion,

    Month(Mês) as Mês,

    Ano,

    "Tipo de Meta",

    "Valor Alvo",

    "Valor Real",

    "Peso da Meta",

    "% Atingido",

    "Peso Final",

    MêsID,

    MetaID

FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Base Gestão de Metas 2016.xlsx]

(ooxml, embedded labels, table is [BD Metas Consolidadas]);

LOAD

    Título,

    Status,

    "Data de início",

    "Data limite",

    "Status Final",

    Gestor as Champion,

    "ID Status"

FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Status Ações.xlsx]

(ooxml, embedded labels, table is Sheet0$);

Re: Error: Garbage after statement

undergrinder — Wed, 01 Jun 2016 07:14:49 GMT

Hi Marina,

At first sight the code is correct. The name of sheet in the second section is really Sheet0$?

I think the $ character is reserved for variables try enclose them in brackets.

LOAD

Título,

Status,

"Data de início",

"Data limite",

"Status Final",

Gestor as Champion,

"ID Status"

FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Status Ações.xlsx]

(ooxml, embedded labels, table is [Sheet0$]);

Or as isaaclin‌ said, try to comment out that section, and see what you get.

Re: Error: Garbage after statement

Thu, 02 Jun 2016 13:00:14 GMT

Hi, I tried to rename the sheet as BD and use [BD] in the code, then rename it back to Sheet0 and use Sheet0$ and [Sheet0] and none of those worked.

Sorry, I missed the comment out part, so here's what I did (got no error message after, it worked):

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='R$ #.##0,00;-R$ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='jan;fev;mar;abr;mai;jun;jul;ago;set;out;nov;dez';

SET DayNames='seg;ter;qua;qui;sex;sáb;dom';

SET LongMonthNames='janeiro;fevereiro;março;abril;maio;junho;julho;agosto;setembro;outubro;novembro;dezembro';

SET LongDayNames='segunda-feira;terça-feira;quarta-feira;quinta-feira;sexta-feira;sábado;domingo';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='pt-BR';

LOAD

    Aeroporto,

    Nível,

    "Função Superior",

    Função,

    Gestor as Champion,

    Month(Mês) as Mês,

    Ano,

    "Tipo de Meta",

    "Valor Alvo",

    "Valor Real",

    "Peso da Meta",

    "% Atingido",

    "Peso Final",

    MêsID,

    MetaID

FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Base Gestão de Metas 2016.xlsx]

(ooxml, embedded labels, table is [BD Metas Consolidadas]);

//LOAD

//    Título,

//    Status,

//    "Data de início",

//    "Data limite",

//    "Status Final",

//    Gestor as Champion,

//    "ID Status"

//FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Status Ações.xlsx]

//(ooxml, embedded labels, table is Sheet0$);

So the problem is in the second LOAD part? I have no idea what could be it since both databases are in the same folder and I think I applied the same code to both cases.

Re: Error: Garbage after statement

isaaclin — Thu, 02 Jun 2016 13:11:03 GMT

Sorry my Spanish is not good. Tried my best to read the statement..

I think since you narrowed down to the second statement , try load field one by one to see which part,

for example, only load Titulo, and bracket Sheet0$ as undergrinder suggested

//LOAD

// Título

//FROM [lib://BD (s-bria-qlikse01_ext-in)/GESTÃO DE METAS/Status Ações.xlsx]

//(ooxml, embedded labels, table is [Sheet0$]);

add one more field if this passed the test.

Good Luck

Re: Error: Garbage after statement

Anonymous — Thu, 02 Jun 2016 13:46:06 GMT

Hi,

Can you attach the excel sheet from which you are loading you can make a copy delete all sensitive data put some sample data and send across...

Thanks

Re: Error: Garbage after statement

Thu, 02 Jun 2016 14:12:06 GMT

Ok, isaaclin, I'll try that, field by field (out of curiosity, that was portuguese!).

I wrote that code in my Qlik Sense Desktop and had no problems with it, it worked fine. When I adapted it for the browser version, the errors started. I hope that testing every field helps, thanks for the patience and support.

gompa786‌, I deleted the column "Título" from the database since it contains texts about the company' strategies. I don't think that will be a problem for your analysis though, since its content are things like "reduce the water consumption". The rest is attached.

Re: Error: Garbage after statement

Thu, 02 Jun 2016 14:17:32 GMT

Ok, isaaclin, I'll try that, field by field (out of curiosity, that was portuguese!).

I wrote that code in my Qlik Sense Desktop and had no problems with it, it worked fine. When I adapted it for the browser version, the errors started. I hope that testing every field helps, thanks for the patience and support.

The Unknown Error persists when I test field by field. None of the rows worked

Re: Error: Garbage after statement

Anonymous — Thu, 02 Jun 2016 15:21:40 GMT

Hi Mariana,

There is only one sheet i.e sheet0 in the excel I thought there were 2 should I go ahead and try with only one sheet..

Thanks

Re: Error: Garbage after statement

Anonymous — Thu, 02 Jun 2016 15:31:26 GMT

Hi Mariana,

Worked absolutely fine for me

Thanks

Re: Error: Garbage after statement

Anonymous — Thu, 02 Jun 2016 15:32:58 GMT

Script scree shot attached:

Thanks

Re: Error: Garbage after statement

Anonymous — Thu, 02 Jun 2016 15:38:05 GMT

Also please find the QVF I have used in the attachment

Re: Error: Garbage after statement

Thu, 02 Jun 2016 17:27:32 GMT

The other sheet is in a different Excel file, in the same folder.

Can I assume it's a communication problem with the folder, then? Does it make sense?

Since this folder is in a network shared with other employees of the company, maybe Qlik Sense is having problems with its path? I thought that wouldn't be the case since both Excel files are there and the first one works.

I'll check your QVF. Thank you so much.

Re: Error: Garbage after statement

isaaclin — Thu, 02 Jun 2016 17:58:59 GMT

I found my code has Biff in the connection. I can load the file with no problems.

LOAD

Status,

"Data de início",

"Data limite",

"Status Final",

Gestor,

"ID Status"

FROM [lib://Excel/Status Ações.xls]

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

and yours is ooxml, gompa786‌code is also showing Biff

not sure why? maybe this causes problem? See my error when using ooxml

Re: Error: Garbage after statement

Thu, 02 Jun 2016 19:21:35 GMT

Still no luck for me

As I said, in my Qlik Sense Desktop it worked. In the Browser version it won't. Maybe it's indeed something with the network, limitting Qlik Sense's access to the file through that path or something like that.