Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

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

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_IDSUPPLIER_NAME
10ABC IT
20EUROPEAN LAB ltd.
30CPH HOTEL

table:     SUP_CATEGORY

SUPPLIER_IDCATEGORY
1010 IT-hardware
2088 Lab equipment

After load data in SUP_CATEGORY should be like this:

SUPPLIER_IDCATEGORY_ID
1010 IT-hardware
2088 Lab equipment
30100 Missing_category

Does this make sense?

Kind regards

Anders, Denmark

Tags (3)
1 Solution

Accepted Solutions

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

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 Replies

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

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.

Not applicable

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

Dear Nicole

Thank you very much.

This worked out just perfectly.

Kind regards

Anders

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

I'm glad it worked for you