Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
alavirona
New Contributor II

Incremental Load - Replace old value

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 :

keyvalues
A

4

B2
C3
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,

1 Solution

Accepted Solutions
Partner
Partner

Re: Incremental Load - Replace old value

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.

View solution in original post

4 Replies
Partner
Partner

Re: Incremental Load - Replace old value

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.

View solution in original post

alavirona
New Contributor II

Re: Incremental Load - Replace old value

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.

Partner
Partner

Re: Incremental Load - Replace old value

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.

alavirona
New Contributor II

Re: Incremental Load - Replace old value

Hi,

There is no other solutions than alterate keys and tables ?