Incremental Load - Replace old value

[Anonymous]

Hello,

I have an issue while trying to do some incremental load. I have old values, then a new one that need to overwrite the old one.

To simplify, I have this :

//list of my keys

KEYS:

LOAD * INLINE [

key

A

B

C

D

];

//First load of values

KEY_VALUES:

LOAD * INLINE [

key, values

A, 1

B, 2

C, 3

];

//new values that need to replace old ones

KEY_VALUES:

LOAD * INLINE [

key, values

A, 4

];

What I want is that for KEY_VALUES table :

key	values
A	4
B	2
C	3
D	-

I wanted to use a WHERE NOT EXISTS(key) but because of my previous definition of the KEYS, I can't (and it would be very complicate to load KEYS after because of dependencies)

Can you help me ?

Thanks,

[felipedl · Accepted solution]

HI aurelien,

WHat I would do is the following:

//list of my keys
// renamed the initial key definition field, for future outer join
KEYS:
LOAD * INLINE [
key_def
A
B
C
D
];

//new values that need to replace old ones
// loaded this code first, since it's the incremental record
KEY_VALUES:
LOAD * INLINE [
key, values
A, 4
];

//First load of values
// loaded the rest of the values, not getting the ones I already loaded
KEY_VALUES:
LOAD * INLINE [
key, values
A, 1
B, 2
C, 3
]
where not Exists(key);

// outer join to get all the possible values for the key field
outer join (KEY_VALUES)
Load
key_def as key
Resident KEYS;

// do store next
store KEY_VALUES into keys.qvd (qvd);

This should update your values and get all the possible key_def fields into your QVD.

Hope it helps.

[Anonymous]

Thank you for your reply felipedl, it work very well !

But what if I have 2 keys ?

//new values that need to replace old ones 

// loaded this code first, since it's the incremental record 

KEY_VALUES: 

LOAD * INLINE [ 

key1, key2, values 

A, 1, 5

]; 

//First load of values 

// loaded the rest of the values, not getting the ones I already loaded 

KEY_VALUES: 

LOAD * INLINE [ 

key1, key2, values 

A, 1, 1

A, 2, 2

B, 2, 3 

C, 1, 1 

] 

where not Exists(key1,key2); 

It show me everyline when I want only :

A, 1, 5

A, 2, 2

B, 2, 3 

C, 1, 1

And not "A, 1, 1" where the key1 and key2 already exist.

[felipedl]

Hi,

Just concatenate both keys into a single field, giving you something like:

KEY_VALUES:

Load

     key1&'_'&key2 as #MasterKey,

     *;

LOAD * INLINE [

key1, key2, values

A, 1, 5

];

after that, you can make the same operation on the #MasterKey field:

where not Exists(#MasterKey)

Felipe.

[Anonymous]

Hi,

There is no other solutions than alterate keys and tables ?