Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
paulosrl
Contributor III
Contributor III

LEFT JOIN PROBLEMS

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!

4 Replies
petter
Partner - Champion III
Partner - Champion III

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
Contributor III
Contributor III
Author

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
Partner - Champion III
Partner - Champion III

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.