Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Here is a sample of debugging method with Qviewer for those who are interesting in it.
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.
can you let us us know what error says?
anant
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
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
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.
João Dias de Carvalho Neto wrote:
Here is a sample of debugging method with Qviewer for those who are interesting in it.
Sounds interesting but unfortunately this is a private group.
Thanks Nitin
The error message confundes me.. The problem was really related at noConcatenate statement
Is your problem got resolved?????????