Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
New Contributor

Insert and update based on condition (without timestamp).

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)

Example_Insert_And_Update.PNG

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

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

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

View solution in original post

4 Replies
Highlighted
Contributor II

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

Refer this post Incremental Load.

Highlighted
New Contributor

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

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

Highlighted
MVP & Luminary
MVP & Luminary

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

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

View solution in original post

Highlighted
New Contributor

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

Thank you Marcus!

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


-Olov