Left join with Null key values

[Morgoz]

Hello,

I have two tables and i need to make a left join between them.

The tables are as follow:

DQ_FACTS_QVD:
LOAD Distinct
    ACCOUNT_ID,
    ID_DQ,
    ACCOUNT_CODE,
    ACCOUNT_CODE_SALESFORCE,
    COMP_REG_NO,
    'check' as Ind
Resident DQ;

DQ_FACTS_INT:
   Load
        ID_DQ,
   	ACCOUNT_ID,
   	ACCOUNT_CODE,
        ACCOUNT_CODE_SALESFORCE,
        ACCOUNT_NAME,
        ACCOUNT_TYPE as ACCOUNT_TYPE_ID,
        COMP_REG_NO
    Resident DQ_FACTS;

   Left Join (DQ_FACTS_INT)
   
   Load *
   Resident DQ_FACTS_QVD

The problem is that the field ACCOUNT_CODE_SALESFORCE in some cases is Null, and in those cases the left join doesn't seems to work. Is it possible to do left joins even if some key fields are null?

Thank you.

[marcus_sommer]

It's not possible to join NULL's as KEY's.

Alternatives and quite often necessities are to handle the NULL's in beforehand by replacing them with the correct value and/or through a default-value. If it's not possible/sensible you may skip those records within the first step and adding them within a second step with a concatenate. Further NULL's may not your only challenge - you may also have missing KEY's and/or duplicates on both sides - and here you may also need to check them to fill the missing values and/or to remove the duplicates.

This means joins could become quite expensive. Much easier would be to replace the join with a mapping which has no risks to remove/duplicate records unwanted and allows also to set a default-value for non-matching KEY's and mappings could be also nested in multiple ways.

- Marcus

[Morgoz]

Thank you for the answer Marcus,

but i don't see the equivalence between a join and a mapping... What i'm trying to do is to "add" the field "Ind" in DQ_FACTS_QVD to the join match's in table DQ_FACTS_INT. How could i do that using mappings?

Thanks again.

[Ksrinivasan]

hi,

try this add drop resident table :

already i have added, please try this

DQ_FACTS_QVD:
LOAD Distinct
    ACCOUNT_ID,
    ID_DQ,
    ACCOUNT_CODE,
    ACCOUNT_CODE_SALESFORCE,
    COMP_REG_NO,
    'check' as Ind
Resident DQ;
Drop table DQ;

DQ_FACTS_INT:
   Load
        ID_DQ,
   	ACCOUNT_ID,
   	ACCOUNT_CODE,
        ACCOUNT_CODE_SALESFORCE,
        ACCOUNT_NAME,
        ACCOUNT_TYPE as ACCOUNT_TYPE_ID,
        COMP_REG_NO
    Resident DQ_FACTS;
Drop Table DQ_FACTS;

   Left Join (DQ_FACTS_INT)
   
   Load *
   Resident DQ_FACTS_QVD

ksrini

[PrashantSangle]

Hi,

Can you explain your requirement with some sample and desired output. It will helpful for providing correct solution.

Regards,

Prashant Sangle

[marcus_sommer]

Take a look here:

Mapping as an Alternative to Joining - Qlik Community - 1473454

Don't join - use Applymap instead - Qlik Community - 1467592

- Marcus