Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Dear Nicole
Thank you very much.
This worked out just perfectly.
Kind regards
Anders
I'm glad it worked for you