Problem Inner Join

Tue, 28 May 2013 17:05:17 GMT

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

Re: Problem Inner Join

Tue, 28 May 2013 17:36:07 GMT

I think,

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

Re: Problem Inner Join

Gysbert_Wassenaar — Tue, 28 May 2013 18:16:44 GMT

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;

Re: Problem Inner Join

Tue, 28 May 2013 18:54:04 GMT

Hello Gysbert, it worked!! Thank you!!

topic Re: Problem Inner Join in QlikView

Problem Inner Join

Re: Problem Inner Join

Re: Problem Inner Join

Re: Problem Inner Join