Problem Inner Join

[#1]

Hello Guys,

I'm having a problem in the code below.

I'm loading a customer table and they have multiple update dates. I need to load the older, but my code is not working.

The function "Max", is not working in my code. She keeps bringing all dates.

Can you help me please?

AuxiliarCliente:

LOAD
[@40:49]  as Cliente,
     Max(Mid(FileName(),17,8)) as [Data de Modificação]
FROM
Inbox\Clientes\INBOX_CLAGCT*.*
(fix, codepage is 1252)
Where ([@9:9]='D')
group by [@40:49];

Cliente:
Load Cliente&[Data de Modificação] as ChaveCliente2
Resident AuxiliarCliente;

DROP Table AuxiliarCliente;

Inner join LOAD

@56:59&@40:49&Mid(FileName(),17,8) as ChaveCliente2,
    If(Left(@40:49,1)<>'0',Mid(@40:49,1,10),
    If(Left(@40:49,2)<>'0',Mid(@40:49,2,10),
    If(Left(@40:49,3)<>'0',Mid(@40:49,3,10),
    If(Left(@40:49,4)<>'0',Mid(@40:49,4,10),
    If(Left(@40:49,5)<>'0',Mid(@40:49,5,10),
    If(Left(@40:49,6)<>'0',Mid(@40:49,6,10),
    If(Left(@40:49,7)<>'0',Mid(@40:49,7,10),
    If(Left(@40:49,8)<>'0',Mid(@40:49,8,10),
    If(Left(@40:49,9)<>'0',Mid(@40:49,9,10), Mid(@40:49,10,10)))))))))) as Cliente,
      @56:59  as Empresa,
      @60:63  as [Grupo de Contas],
      @64:78  as Tratamento,
      @79:113  as [Nome Cliente],
      @219:253  as Endereco,
      @264:298  as [Cidade Cliente],
      @299:308  as CEP,
      @309:353  as Bairro,
      @354:355  as Pais,
      @357:358  as [UF Cliente],
      @790:790 as [Tipo Alteração],
      Mid(FileName(),23,2)&'/'&Mid(FileName(),21,2)&'/'&Mid(FileName(),17,4) as [Data de Modificação]
FROM
Inbox\Clientes\Inbox_CL*.*
(fix, codepage is 1252)
WHERE([@354:356]= 'BR');

[#2]

I think,

If

The function "Max", is not working in your code.

And If you find the max of dates then you write as

max(date(Mid(FileName(),17,8)))

Regards

vishwaranjan

[Gysbert_Wassenaar · Accepted solution]

The max function is done for each loaded file separately. Try this:

Temp:

LOAD
[@40:49]  as Cliente,
     date#(Mid(FileName(),17,8),'YYYYMMDD') as [Data de Modificação]
FROM
Inbox\Clientes\INBOX_CLAGCT*.*
(fix, codepage is 1252)
Where ([@9:9]='D');

AuxiliarCliente:

noconcatenate LOAD
Cliente,
     Max([Data de Modificação]) as [Data de Modificação]
Resident Temp
group by Cliente;

Drop table Temp;

[#4]

Hello Gysbert, it worked!! Thank you!!