3 Replies Latest reply: May 28, 2013 2:54 PM by LEONARDO GIULIANETTI RSS

    Problem Inner Join

      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');