If a record in one table do not have a record in another table .. add it

[#1]

Dear Qlikview Community

The hardest part about this question was to find a suiting headline.

The issue at hand is that all suppliers (100.000+) is categorized in one table (SUP_CATEGORY). In a SUPPLIER table all information about our Supplier from Oracle is stored. New suppliers in SUPPLIER will not have a record in SUP_CATEGORY. During the load I want the script to add all new suppliers to SUP_CATEGORY with a default category (MISSING CATEGORY)

table:     SUPPLIER

SUPPLIER_ID	SUPPLIER_NAME
10	ABC IT
20	EUROPEAN LAB ltd.
30	CPH HOTEL

table:     SUP_CATEGORY

SUPPLIER_ID	CATEGORY
10	10 IT-hardware
20	88 Lab equipment

After load data in SUP_CATEGORY should be like this:

SUPPLIER_ID	CATEGORY_ID
10	10 IT-hardware
20	88 Lab equipment
30	100 Missing_category

Does this make sense?

Kind regards

Anders, Denmark

[Nicole-Smith · Accepted solution]

This should do the trick:

SUPPLIER:

LOAD * INLINE [

    SUPPLIER_ID, SUPPLIER_NAME

    10, ABC IT

    20, EUROPEAN LAB ltd.

    30, CPH HOTEL

];

SUP_CATEGORY:

LOAD * INLINE [

    SUPPLIER_ID, CATEGORY

    10, 10 IT-hardware

    20, 88 Lab equipment

];

CONCATENATE (SUP_CATEGORY)

LOAD SUPPLIER_ID,

     '100 Missing_category' as CATEGORY

RESIDENT SUPPLIER

WHERE IsNull(Lookup('CATEGORY', 'SUPPLIER_ID', SUPPLIER_ID, 'SUP_CATEGORY'));

Example file is also attached.

[#3]

Dear Nicole

Thank you very much.

This worked out just perfectly.

Kind regards

Anders

[Nicole-Smith]

I'm glad it worked for you