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: 
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

1 Solution

Accepted Solutions
Nicole-Smith

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.

View solution in original post

3 Replies
Nicole-Smith

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
Author

Dear Nicole

Thank you very much.

This worked out just perfectly.

Kind regards

Anders

Nicole-Smith

I'm glad it worked for you