topic Re: LEFT JOIN PROBLEMS in QlikView

LEFT JOIN PROBLEMS

paulosrl — Fri, 10 Mar 2017 22:48:17 GMT

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!

Re: LEFT JOIN PROBLEMS

petter — Fri, 10 Mar 2017 23:11:39 GMT

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.

Re: LEFT JOIN PROBLEMS

paulosrl — Sat, 11 Mar 2017 12:33:45 GMT

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.

Re: LEFT JOIN PROBLEMS

rubenmarin — Sat, 11 Mar 2017 15:53:33 GMT

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 ...

Re: LEFT JOIN PROBLEMS

petter — Wed, 15 Mar 2017 11:46:36 GMT

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.