Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

1 Solution

Accepted Solutions

Re: Problem Inner Join

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;


talk is cheap, supply exceeds demand
3 Replies
Not applicable

Re: Problem Inner Join

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

Re: Problem Inner Join

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Problem Inner Join

Hello Gysbert, it worked!! Thank you!!

Community Browser