Working with NULLs in LEFT JOIN (Script)

[rafaeloneil]

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:

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

Could you help me solve this problem?

Thanks in advance!

[rafaeloneil]

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]

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]

I marked the answer as Helpful, Thank you