topic Re: Insert and update based on condition (without timestamp). in QlikView

Insert and update based on condition (without timestamp).

Anonymous — Thu, 08 Dec 2016 09:19:02 GMT

I have encountered a problem when I am trying to write a script to one version of "Insert and Update" Incremental Load.

Example: To simplify the example I have made an illustration of how I want the data-set to update. (I leave the code for the discussion)

In the example above you can see that I both want insert new records and to update records. The condition is that I only want to update records if the new value is greater than the existing record.

For instance, the existing record for ID 2 equals to 0 (Table 1) and since the new record for ID 2 equals to 100 (Table 2) I want to Update that record so the final updated record for ID 2 equals to the highest value of them two (Updated Table). If Table 2 contains a new record I just want to add that record to the final data-set.

Description:

1. Insert new records

2. Update records if the value is higher than the existing record

What do you guys think is the best solution for this kind of problem?

Best regards,

Olov

Re: Insert and update based on condition (without timestamp).

Anonymous — Thu, 08 Dec 2016 14:01:20 GMT

Refer this post Incremental Load.

Re: Insert and update based on condition (without timestamp).

Anonymous — Thu, 08 Dec 2016 14:09:43 GMT

I understand the idea with incremental load but this problem does not have a modification-date as described in the refered post.

Re: Insert and update based on condition (without timestamp).

marcus_sommer — Thu, 08 Dec 2016 19:03:49 GMT

You could try something like this:

t1:

load * inline [

ID, Value

1,500

2,0

3,100

4,300

5,0

];

t2:

noconcatenate load * inline [

ID, Value

2,100

3,700

4,300

6,500

7,0

];

t3:

noconcatenate load * resident t1;

join(t3)

load ID, Value as ValueN resident t2;

t4:

noconcatenate load ID, rangemax(Value, ValueN) as Value resident t3;

drop tables t1, t2, t3;

- Marcus

Re: Insert and update based on condition (without timestamp).

Anonymous — Fri, 09 Dec 2016 08:45:10 GMT

Thank you Marcus!

I really appreciate that you took some time helping me out.

-Olov