topic Re: SUM raises general script error in QlikView

SUM raises general script error

Mon, 30 Dec 2013 11:02:11 GMT

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

Re: SUM raises general script error

Mon, 30 Dec 2013 11:07:02 GMT

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-runtime-with-qviewer

Re: SUM raises general script error

Anonymous — Mon, 30 Dec 2013 11:25:55 GMT

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.

Re: SUM raises general script error

Anonymous — Mon, 30 Dec 2013 11:50:32 GMT

can you let us us know what error says?

anant

Re: Re: SUM raises general script error

Mon, 30 Dec 2013 11:51:22 GMT

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

Re: SUM raises general script error

Mon, 30 Dec 2013 11:55:36 GMT

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

Re: SUM raises general script error

Mon, 30 Dec 2013 12:01:44 GMT

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.

Re: SUM raises general script error

Mon, 30 Dec 2013 12:03:31 GMT

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-runtime-with-qviewer

Sounds interesting but unfortunately this is a private group.

Re: SUM raises general script error

Mon, 30 Dec 2013 12:11:20 GMT

Thanks Nitin

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

Re: SUM raises general script error

Anonymous — Mon, 30 Dec 2013 12:11:58 GMT

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

Re: SUM raises general script error

Mon, 30 Dec 2013 12:14:58 GMT

Yeap.

I really do not understand why some columns SUM properly and another one raises error, but setting the noConcatenate command all SUM expressions works correctly

Re: SUM raises general script error

Anonymous — Mon, 30 Dec 2013 12:21:52 GMT

It happens when we are using aggreate functions with the resident load and not using noconcatenate.

Therefore always try to use it, or just store the previous table in qvd and then drop it.

Now when you will load the final table from qvd, you can easily apply any aggreate functions.

I also had this problem previously, thats why I suggested you to use it.

Any way, it gud to get resolved the issue,

Have a nice day and Happy new year in advance.

Re: SUM raises general script error

Mon, 30 Dec 2013 12:24:39 GMT

Thanks Nitin...

I wish a happy new year too