Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rafaeloneil
Contributor III
Contributor III

Working with NULLs in LEFT JOIN (Script)

Guys, good afternoon,


I'm having a problem working with null fields in QlikView:
I join a table coming from SYBASE with another one coming from Excel as script below:


SELECT

'LSG' AS 'PRODUTO',

'COMERCIAL VEICULO'  AS 'CANAL',

ct.CdOperador AS 'CDCOLABORADOR',

fl.NmRazaoSocial AS 'NMFILIAL',

COUNT(*) AS 'CARTEIRA',

2 AS 'TPQUERY',

CONVERT(date, getdate()) AS 'DTFOTO'

FROM

DBGESTAOLSG..TbContrato ct,

DBCOR..TbFilial fl

WHERE

  ct.CdFilial = fl.CdFilial

AND ct.DtLiquidacaoEfetiva IS NULL

AND ct.DtCancelamento IS NULL

AND fl.DsFilial NOT LIKE 'CP%'

AND ct.DtContrato <= DATEADD(DAY, -19, GETDATE())

GROUP BY ct.CdOperador, fl.NmRazaoSocial;

LEFT JOIN (TB_CPVEIC)

TBHIERARQUIA_CARTCPVEIC:

LOAD OPERADOR_CDCOLAB AS CDCOLABORADOR,

    IF(IsNull(NOME_REGIONAL), 'Nao Localizado', NOME_REGIONAL) AS REGIONAL_CARTCPVEIC

FROM

(ooxml, embedded labels, table is Base_Hierarquia);


According to script, I wish that AFTER joining the 2 tables (LEFT JOIN) the null values:

Table.png

Instead of being Null as picture above, be defined as 'Not Localized'


Could you help me solve this problem?

Thanks in advance!

6 Replies
rafaeloneil
Contributor III
Contributor III
Author

In time, the complete script is this:


TB_CPVEIC:

SELECT

'LSG' AS 'PRODUTO',

'COMERCIAL VEICULO'  AS 'CANAL',

ct.CdOperador AS 'CDCOLABORADOR',

fl.NmRazaoSocial AS 'NMFILIAL',

COUNT(*) AS 'CARTEIRA',

2 AS 'TPQUERY',

CONVERT(date, getdate()) AS 'DTFOTO'

FROM

DBGESTAOLSG..TbContrato ct,

DBCOR..TbFilial fl

WHERE

  ct.CdFilial = fl.CdFilial

AND ct.DtLiquidacaoEfetiva IS NULL

AND ct.DtCancelamento IS NULL

AND fl.DsFilial NOT LIKE 'CP%'

AND ct.DtContrato <= DATEADD(DAY, -19, GETDATE())

GROUP BY ct.CdOperador, fl.NmRazaoSocial;

LEFT JOIN (TB_CPVEIC)

TBHIERARQUIA_CARTCPVEIC:

LOAD OPERADOR_CDCOLAB AS CDCOLABORADOR,

IF(IsNull(NOME_REGIONAL), 'Nao Localizado', NOME_REGIONAL) AS REGIONAL_CARTCPVEIC

FROM

(ooxml, embedded labels, table is Base_Hierarquia);

sunny_talwar

You will have to add a resident load to fix this

TB_CPVEIC:

SELECT

'LSG' AS 'PRODUTO',

'COMERCIAL VEICULO'  AS 'CANAL',

ct.CdOperador AS 'CDCOLABORADOR',

fl.NmRazaoSocial AS 'NMFILIAL',

COUNT(*) AS 'CARTEIRA',

2 AS 'TPQUERY',

CONVERT(date, getdate()) AS 'DTFOTO'

FROM

DBGESTAOLSG..TbContrato ct,

DBCOR..TbFilial fl

WHERE

  ct.CdFilial = fl.CdFilial

AND ct.DtLiquidacaoEfetiva IS NULL

AND ct.DtCancelamento IS NULL

AND fl.DsFilial NOT LIKE 'CP%'

AND ct.DtContrato <= DATEADD(DAY, -19, GETDATE())

GROUP BY ct.CdOperador, fl.NmRazaoSocial;

LEFT JOIN (TB_CPVEIC)

TBHIERARQUIA_CARTCPVEIC:

LOAD OPERADOR_CDCOLAB AS CDCOLABORADOR,

    NOME_REGIONAL

FROM

(ooxml, embedded labels, table is Base_Hierarquia);

Final_TB_CPVEIC

LOAD *,

      If(Len(Trim(NOME_REGIONAL)) = 0, 'Nao Localizado', NOME_REGIONAL) AS REGIONAL_CARTCPVEIC

Resident TB_CPVEIC;

DROP Table TB_CPVEIC;

OmarBenSalem

do a mapping load instead in that case:


TBHIERARQUIA_CARTCPVEIC:

MAPPING LOAD OPERADOR_CDCOLAB AS CDCOLABORADOR,

    IF(IsNull(NOME_REGIONAL), 'Nao Localizado', NOME_REGIONAL) AS REGIONAL_CARTCPVEIC

FROM

(ooxml, embedded labels, table is Base_Hierarquia);

load *,

applyMap('TBHIERARQUIA_CARTCPVEIC',CDCOLABORADOR,'Not Localized') as RegionalField

;

SELECT

'LSG' AS 'PRODUTO',

'COMERCIAL VEICULO'  AS 'CANAL',

ct.CdOperador AS 'CDCOLABORADOR',

fl.NmRazaoSocial AS 'NMFILIAL',

COUNT(*) AS 'CARTEIRA',

2 AS 'TPQUERY',

CONVERT(date, getdate()) AS 'DTFOTO'

FROM

DBGESTAOLSG..TbContrato ct,

DBCOR..TbFilial fl

WHERE

  ct.CdFilial = fl.CdFilial

AND ct.DtLiquidacaoEfetiva IS NULL

AND ct.DtCancelamento IS NULL

AND fl.DsFilial NOT LIKE 'CP%'

AND ct.DtContrato <= DATEADD(DAY, -19, GETDATE())

GROUP BY ct.CdOperador, fl.NmRazaoSocial;

rafaeloneil
Contributor III
Contributor III
Author

I used the Script option and it worked, Once again, thanks for the help !!

OmarBenSalem

If it's all working fine; please close the thread by marking the correct answer as so.

Have a nice day

rafaeloneil
Contributor III
Contributor III
Author

I marked the answer as Helpful, Thank you