topic Re: Treat null values coming from an automatic join of two tables in App Development

Treat null values coming from an automatic join of two tables

lailarhc — Tue, 30 Nov 2021 18:17:33 GMT

Hello.
I have two tables where qlik makes a join with the column 'Servidor'.

I want the resulting tuples to treat the null values as an empty string. But it's not working.

Here's my script:

LIB CONNECT TO 'anatelbdro01 - D-1 de produção';

NullAsValue *;

Set Nullvalue = '';

LOAD Servidor,
Início,
Fim,
Horas,
Curso;
[cursos]:
SQL SELECT NomeEmpregado as Servidor,
evento."DataIniRealizacao" as Início,
evento."DataFimRealizacao" as Fim,
evento."CargaHoraria" as Horas,
evento."descEvento" as Curso
FROM SARH.dbo.Empregado
LEFT JOIN SDTA.dbo."V_AuditoriaEventoServidor" as evento
ON evento.numCpf = Empregado.NumCpf
LEFT JOIN SARH.dbo.DadosFuncionais as dados
ON dados.CodEmpregado = Empregado.CodEmpregado
JOIN SARH.dbo.Orgao as orgao
ON orgao.CodOrgao = dados.CodUltimaLotacao
AND orgao.SiglaOrgao LIKE 'GR08%'
WHERE dados.DataSaidaEmpregado is null

UNION

SELECT NomeEmpregado as Servidor2,
capacitacao."DataInicio" as Início2,
capacitacao."DataFim" as Fim2,
capacitacao."CargaHoraria" as Horas2,
capacitacao."descTitulo" as Curso2
FROM SARH.dbo.Empregado
LEFT JOIN SDTA.dbo."SHC_CapacitacaoNaoInstitucional" as capacitacao
ON capacitacao.CodEmpregado = Empregado.CodEmpregado
LEFT JOIN SARH.dbo.DadosFuncionais as dados
ON dados.CodEmpregado = Empregado.CodEmpregado
JOIN SARH.dbo.Orgao as orgao
ON orgao.CodOrgao = dados.CodUltimaLotacao
AND orgao.SiglaOrgao LIKE 'GR08%'
WHERE dados.DataSaidaEmpregado is null;

[cursos_sugeridos]:
LOAD
[Titulos],
[Servidor]
FROM [lib://AttachedFiles/Indicação de cursos_2.xlsx]
(ooxml, embedded labels, table is Planilha2);

I'm using NullAsValue but I still get the following:

At the same time that I also have:

With empty strings instead of null values.

Why is this happening? How can I fix this?
Thank you in advance.

Re: Treat null values coming from an automatic join of two tables

rbartley — Mon, 21 Jun 2021 14:34:00 GMT

Hi,

A couple of points: 1) Why are you renaming the columns in your UNION select statement e.g .... as Servidor2? If they are being unioned, then I assume they will use the field name in the first part of the UNION query, i.e. Servidor instead of Servidor2.

2) in the query for the table [cursos_sugeridos] , you are doing a straight load, i.e. an equi-join, but I imagine you should be doing a left join, so you join all of the records in table 1 AND those that match in your Excel file, and for unmatched records, there will be no suggested courses. I don't think your NullAsValue function will work without the left join as the record will simply not exist.

Let me know if I've misinterpreted something and, if so, it would be useful if you attached your qvf (app) file.

Re: Treat null values coming from an automatic join of two tables

lailarhc — Mon, 21 Jun 2021 16:23:27 GMT

You're right about the renaming of the columns. I fixed that as there was no need for them to be named differently.
As for the left load, I actually need all the records in both tables. 😕
I need the records in excel file for another chart table, so I don't think I should use a Left join.

And I'm using the cloud version, so I don't have a qvp file. 😕

Let me know if there's anything else I can do to clarify my problem.
And thank you for answering. 🙂

Re: Treat null values coming from an automatic join of two tables

rbartley — Tue, 22 Jun 2021 15:05:24 GMT

Could you extract the data from the individual tables into Excel files so that I can take a look? Alter any confidential data before attaching it.

Re: Treat null values coming from an automatic join of two tables

lailarhc — Wed, 23 Jun 2021 11:16:22 GMT

Hello 🙂

I managed to do what I wanted by doing an outer join with the tables.
Here's what my script looks like now:

LIB CONNECT TO 'anatelbdro01 - D-1 de produção';

NullAsValue *;

Set Nullvalue = '';

UNION

SELECT NomeEmpregado as Servidor,
capacitacao."DataInicio" as Início,
capacitacao."DataFim" as Fim,
capacitacao."CargaHoraria" as Horas,
capacitacao."descTitulo" as Curso
FROM SARH.dbo.Empregado
LEFT JOIN SDTA.dbo."SHC_CapacitacaoNaoInstitucional" as capacitacao
ON capacitacao.CodEmpregado = Empregado.CodEmpregado
LEFT JOIN SARH.dbo.DadosFuncionais as dados
ON dados.CodEmpregado = Empregado.CodEmpregado
JOIN SARH.dbo.Orgao as orgao
ON orgao.CodOrgao = dados.CodUltimaLotacao
AND orgao.SiglaOrgao LIKE 'GR08%'
WHERE dados.DataSaidaEmpregado is null;

outer Join(cursos)
LOAD
[Titulos],
[Servidor]
FROM [lib://AttachedFiles/Indicação de cursos_2.xlsx]
(ooxml, embedded labels, table is Planilha2);

Now I have a single table where all the null values are replaced with an empty string.
I actually got the idea from your first comment, so thank you!

I'll be closing this post and marking it as solved. ^^

Re: Treat null values coming from an automatic join of two tables

rbartley — Wed, 23 Jun 2021 13:00:24 GMT

Ok. Glad it helped.