Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
Highlighted
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
Highlighted
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 ?