Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SUM raises general script error

Hi all,

I am trying to execute a load statement that´s concatenate a lot of tables then sum it´s columns in another one. Such like this

LET PED_STATUS_ABERTO_TOTAL = 1;

LET PED_STATUS_ABERTO_PARCIAL = 2;

LET PED_STATUS_LIQUIDADO = 4;

LET PED_STATUS_CANCELADO = 5;

PassiveisDeCancelamento:

NoConcatenate

LOAD *,

    0 AS CAN_QTDCAN,

    0 AS CAN_VLRCAN,

  if(PED_INTEGR = 'Integrado', PED_QTDPED/PED_VLRCNV, PED_QTDPED) as PAS_QTDPAS,

  if(PED_INTEGR = 'Integrado', (PED_QTDPED/PED_VLRCNV) * (PED_PREUNI*PED_VLRCNV), PED_QTDPED * PED_PREUNI)  as PAS_VLRPAS

RESIDENT Pedido

WHERE PED_SITPED = $(PED_STATUS_ABERTO_PARCIAL)

  AND PED_QTDCAN > 0;

TotalmenteCancelados:

NoConcatenate

LOAD *,

  if(PED_INTEGR = 'Integrado', PED_QTDPED/PED_VLRCNV, PED_QTDPED) as CAN_QTDCAN,

  (if(PED_INTEGR = 'Integrado', (PED_QTDPED/PED_VLRCNV)*(PED_PREUNI*PED_VLRCNV), PED_QTDPED*PED_VLRCNV)) as CAN_VLRCAN,

  0 AS PAS_QTDPAS,

  0 AS PAS_VLRPAS

RESIDENT Pedido

WHERE PED_SITPED = $(PED_STATUS_CANCELADO);

SaldoFaturadoVsPedido:

NoConcatenate

LOAD *,

    if(PED_INTEGR = 'Integrado', PED_QTDFAT/PED_VLRCNV, PED_QTDPED) AS FAT_QTDFAT,

  if(PED_INTEGR = 'Integrado', PED_QTDPED/PED_VLRCNV, PED_QTDPED) AS FAT_QTDPED,

  if(PED_INTEGR = 'Integrado', PED_PREUNI*PED_VLRCNV, PED_PREUNI) AS FAT_VLRUNI

RESIDENT Pedido

WHERE PED_SITPED = $(PED_STATUS_LIQUIDADO);

CancelamentoDeLiquidados:

NoConcatenate

LOAD *,

  if(FAT_QTDPED-FAT_QTDFAT > 0, FAT_QTDPED-FAT_QTDFAT, 0) AS CAN_QTDCAN,

  if(FAT_QTDPED-FAT_QTDFAT > 0, FAT_QTDPED*FAT_VLRUNI, 0) AS CAN_VLRCAN,

  0 AS PAS_QTDPAS,

  0 AS PAS_VLRPAS

RESIDENT SaldoFaturadoVsPedido;

TCancelamentos:

LOAD DISTINCT

  PED_PEDFIL AS CAN_PEDFIL,

  PED_PEDCOD AS CAN_PEDCOD,

  PED_PEDSEQ AS CAN_PEDSEQ,

  PED_PROCOD AS CAN_PROCOD,

  CAN_QTDCAN,

  CAN_VLRCAN,

  PAS_QTDPAS,

  PAS_VLRPAS

RESIDENT CancelamentoDeLiquidados;

CONCATENATE

LOAD DISTINCT

  PED_PEDFIL AS CAN_PEDFIL,

  PED_PEDCOD AS CAN_PEDCOD,

  PED_PEDSEQ AS CAN_PEDSEQ,

  PED_PROCOD AS CAN_PROCOD,

  CAN_QTDCAN,

  CAN_VLRCAN,

  PAS_QTDPAS,

  PAS_VLRPAS

Resident TotalmenteCancelados;

CONCATENATE

LOAD DISTINCT

  PED_PEDFIL AS CAN_PEDFIL,

  PED_PEDCOD AS CAN_PEDCOD,

  PED_PEDSEQ AS CAN_PEDSEQ,

  PED_PROCOD AS CAN_PROCOD,

  CAN_QTDCAN,

  CAN_VLRCAN,

  PAS_QTDPAS,

  PAS_VLRPAS

RESIDENT PassiveisDeCancelamento;

//Debugging statement

call inspect ('TCancelamentos')

Cancelamentos:

LOAD

  CAN_PEDFIL,

  CAN_PEDCOD,

  CAN_PEDSEQ,

  CAN_PROCOD,

  SUM(CAN_QTDCAN) AS CAN_QTDCAN,

  SUM(CAN_VLRCAN) AS CAN_VLRCAN,

  SUM(PAS_QTDPAS) AS PAS_QTDPAS,

  SUM(PAS_VLRPAS) AS PAS_VLRPAS

RESIDENT TCancelamentos

GROUP BY

  CAN_PEDFIL,

  CAN_PEDCOD,

  CAN_PEDSEQ,

  CAN_PROCOD;

//Debugging statement

call inspect('Cancelamentos')

The debugging statement is a function to inspect data at resident tables... the fact is with SUM expressions at 'Cancelamentos' resident table the script raises an error. If I let only simple columns without SUM expression the script loads fine..

Does someone have one idea that what could I doing wrong?

Thanks in advance

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Use noconcatenate befor you are loading your table containing sum expression.

As when you are not using it, it will try to concatenate with previous loaded table and provide you error.


View solution in original post

12 Replies
Not applicable
Author

Here is a sample of debugging method with Qviewer for those who are interesting in it.

http://community.qlik.com/groups/qlikview-brasil/blog/2013/12/27/inspecting-qvd-files-at-script-runt...

Anonymous
Not applicable
Author

Use noconcatenate befor you are loading your table containing sum expression.

As when you are not using it, it will try to concatenate with previous loaded table and provide you error.


Anonymous
Not applicable
Author

can you let us us know what error says?

anant

Not applicable
Author

Hi Nitin,

Thanks for help.

So do I think that the problem is in the NoConcatenate, but I do no think so...

hacking a little bit, at "Cancelamentos" if I let the columns:

  SUM(CAN_QTDCAN) AS CAN_QTDCAN,

  SUM(CAN_VLRCAN) AS CAN_VLRCAN,

  SUM(PAS_QTDPAS) AS PAS_QTDPAS

The scripts works fine.

I think the problem is the SUM(PAS_VLRPAS) AS PAS_VLRPAS column.

In the first I think that was a NULL state at this column and this is raising the error at SUM, but there are no Null values at 'TCancelamentos' resident table

The behavior is so strange.. At debug time the load statement executes like the table was created properly, but when I try to store into a QVD file the Qlikview says that Table does not exists.

Please take a look at TCancelamentos Data

Not applicable
Author

Hi Anant,

The behavior is strange.. The load statement executes at step by step debug but the Qlikview said that table does not exists at store command

Not applicable
Author

João Dias de Carvalho Neto wrote:

...Qlikview said that table does not exists at store command

It looks like the table is loaded but then concatenated into the previous table, meaning the table no longer exists when trying to store.

João Dias de Carvalho Neto wrote:

I think the problem is the SUM(PAS_VLRPAS) AS PAS_VLRPAS column.

Maybe QlikView sees PAS_VLRPAS as a string? QlikView normally handles leading/trailing spaces well but it is worth double-checking or trimming.

Not applicable
Author

João Dias de Carvalho Neto wrote:

Here is a sample of debugging method with Qviewer for those who are interesting in it.

http://community.qlik.com/groups/qlikview-brasil/blog/2013/12/27/inspecting-qvd-files-at-script-runt...

Sounds interesting but unfortunately this is a private group.

Not applicable
Author

Thanks Nitin

The error message confundes me..  The problem was really related at noConcatenate statement

Anonymous
Not applicable
Author


Is your problem got resolved?????????