topic Incremental Load - Replace old value in QlikView

Incremental Load - Replace old value

Anonymous — Tue, 17 Jul 2018 14:47:18 GMT

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

];

//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,

Re: Incremental Load - Replace old value

felipedl — Tue, 17 Jul 2018 16:05:56 GMT

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.

Re: Incremental Load - Replace old value

Anonymous — Thu, 19 Jul 2018 12:03:58 GMT

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.

Re: Incremental Load - Replace old value

felipedl — Thu, 19 Jul 2018 16:18:20 GMT

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.

Re: Incremental Load - Replace old value

Anonymous — Fri, 20 Jul 2018 07:27:47 GMT

Hi,

There is no other solutions than alterate keys and tables ?