Dados Duplicados/ Sum somando em dobro

[analaurarui]

Olá

Estou com problemas para carregar os dados.  Mesmo quando faço load distinct, algumas linhas somam o saldo em dobro.

Segue o codigo:

LIB CONNECT TO 'Protheus';

B8_B1:

SELECT

B8.B8_LOTECTL,
concat(B8.B8_FILIAL,'-',B8.B8_LOCAL) as [Chave Filial-Local],
B8.B8_PRODUTO as "Cód. Produto",
B8.B8_DTVALID as "Data Validade",
B8.B8_SALDO,
B1.B1_PRVALID as "Prazo Validade",
B1.B1_TIPO,
B1.B1_COD,
concat(B8.B8_LOTECTL,'-',B8.B8_FILIAL,'-',B8.B8_LOCAL,'-',B8.B8_PRODUTO,'-',B8.B8_DTVALID,'-',B8.B8_SALDO,'-',B1.B1_TIPO,'-', B1.B1_COD) as Key_1

FROM "PROTHEUS_PRODUCAO".dbo.SB8010 as B8

Left Join "PROTHEUS_PRODUCAO".dbo.SB1010 as B1

on B8.B8_PRODUTO = B1.B1_COD

where (concat(B8.B8_FILIAL,'-',B8.B8_LOCAL) in ('0111-20' ,'0111-87' , '0108-20' , '0105-20')) and B8.B8_SALDO > '0' and B1.B1_TIPO = 'PA' and B8.D_E_L_E_T_ = '';

B8_B1_Shelf:

Load Distinct

[Chave Filial-Local] as [Filial-Local],
[Cód. Produto] as [Cód. Produto 2],
if(Date(Date#([Data Validade], 'YYYYMMDD')) >= today(), Date(Date#([Data Validade], 'YYYYMMDD')), null()) AS [Data Validade 2],
[B8_LOTECTL] as [Lote],
[B8_SALDO] as [Saldo],
[Prazo Validade] as [Prazo de Validade 2],
[B1_TIPO] as Tipo,
[B1_COD] as "Cód. B1",
(Date(Date#([Data Validade], 'YYYYMMDD') ))-today() AS [Dias Restantes],
((Date(Date#([Data Validade], 'YYYYMMDD') )) - today())/[Prazo Validade] AS [Vida Restante],
(1-(((Date(Date#([Data Validade], 'YYYYMMDD')))-today())/[Prazo Validade])) AS [% Vivida],
B8_LOTECTL&'-'&[Chave Filial-Local]&'-'&[Cód. Produto]&'-'&[Data Validade]&'-'&[B8_SALDO]&'-'&B1_TIPO&'-'&[B1_COD] as Key_1

Resident B8_B1;

drop table B8_B1;

Inner Join (B8_B1_Shelf)

Load

[Lote],
max([Prazo de Validade 2]) as MaxPrazo ,
max([% Vivida]) as Max%Vivida,
if(Max([% Vivida])>=0 and Max([% Vivida]) <= 0.33, '33%', if(Max([% Vivida])> 0.33 and Max([% Vivida]) <= 0.5, '50%', if(Max([% Vivida]) > 0.5 and Max([% Vivida]) <=0.6, '60%', if(Max([% Vivida]) > 0.6 and Max([% Vivida]) <= 0.7, '70%', if(Max([% Vivida]) >0.7 and Max([% Vivida]) <= 0.8, '80%', if(Max([% Vivida]) > 0.8 and Max([% Vivida]) <= 0.9, '90%', if(Max([% Vivida]) > 0.9 and Max([% Vivida]) <=1, 'Maior que 90%'))))))) as [Shelf Life %]

resident B8_B1_Shelf

group by [Lote];

[JordyWegman]

Hi,

The easiest way to tackle this problem is loading step by step.

1. First try the PROTHEUS_PRODUCAO. Load just one field (preferably your key field) this and see if there are duplicates
2. If there are duplicates, try to resident load this with a Load distinct.

Now work down your list and see if your end result has still duplicates.

If you don't have any duplicated, try adding more fields. If you then find duplicates, you know which field is causing this.

Jordy

Climber