LEFT JOIN PROBLEMS

[paulosrl]

Hi all,

I'm trying a left join to get just one field from one table to put another, but the field is empty after join.

The script:

EFINAL:

LOAD

     NUMERO_BANCO ,

     NUMERO_AGENCIA ,

     NUMERO_CONTA ,

     DATA_LANCAMENTO ,

     NUMERO_DOCUMENTO ,

     DESCRICAO_LANCAMENTO ,

     NATUREZA_LANCAMENTO

FROM

[..\QVD\EXTRATO_DETALHADO.QVD]

(qvd);

left join

EXTRATO:

LOAD 

     NUMERO_BANCO ,

     NUMERO_AGENCIA ,

     NUMERO_CONTA ,

     DATA_LANCAMENTO ,

     NUMERO_DOCUMENTO ,

     DESCRICAO_LANCAMENTO ,

     NATUREZA_LANCAMENTO ,

     TIPO_LANCAMENTO

FROM

[..\QVD\EXTRATO.QVD]

(qvd);

STORE EFINAL INTO [..\QVD\EFINAL.QVD];

Thanks!

[petter]

This looks perfectly alright to me... do you have some example app to share?

Have you checked that you get any actual match on all the fields between the two tables? If there is no match the TIPO_LANCAMENTO will be empty - that is how a left join works.

[paulosrl]

Hi Petter, the problem is the data content, there are some data using '000' left other no! 🙂 . I need clean the database or try using  somenthing like Purgechar(num(NUMERO_CONTA ,'00000000000000000000'),'') as Conta.

The problem stay in all fields 🙂 ...'0000' left ...

Thanks for your answer.

[rubenmarin]

Hi Paulo, if the first character expected is a number distinct than zero (not a letter) you can use:

Mid(NUMERO_CONTA, FindOneOf(NUMERO_CONTA, '123456789')) as ...

[petter]

It that what you are struggling with here is the number formatting.

The quickest way to solve this is to force QlikView to format you numbers in a "standard" way instead of it trying to intelligently keep the number format that is in your source data.

In your load script you can do something like this:

Num(NUMERO_CONTA)

Then QlikView will use the number format that is specified in your SET environment variable that would be the same as the setting on your Windows computer. If you put in the string with a lot of zeros what QlikView will then do is to have a lot of preceding zeros for all of your numbers which is probably not your intention.